With an inner join you will get one master row for each detail row, yes.
I thought you wanted one row per master record, no matter what, and in
one result set column and indication of whether the detail table
contains at least one matching record or not. Can't do that with an
inner join as far as I can see.

Kjell

dinol.softedge wrote:

> Thanks Helen and Kjell
>
> Helen won't that create multiple rows if there is more than one
> employee? I need only one row per job card and all I'm really trying
> to do is find out if there is an employee or not
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Helen Borrie <helebor@...>
> wrote:
> >
> > At 18:18 12/10/2008, you wrote:
> > >Hi
> > >
> > >Sorry for not being clearer. I don't need to count the employees,
> > >just find out if there is at least one per record in the tbl_job_card
> > >table
> >
> > You only need an inner join to make this happen:
> >
> > SELECT
> > tjc.EXCESS_PAID,
> > tjc.JOB_CARD_ID,
> > tjc.TIME_START,
> > tjc.TIME_FINISH,
> > tjc....l
> > tjc.TIME_TAKEN,
> > tc.ADDRESS AS CLIENT_ADDRESS,
> > tc.SUBURB AS CLIENT_SUBURB,
> > tc.....,
> > tjc.ADVERT,
> > ti.COMPANY AS INSURER_COMPANY,
> > ti...,
> > tc.TBL_CLIENT.TITLE || ' ' || tc.SURNAME || ', ' || tjc.JOB_TYPE
> AS CAPTION,
> > ...
> > tjc.COORDINATES,
> > tjc.VAT
> > FROM
> > TBL_JOB_CARD tjc
> > join TBL_JOB_CARD_EMPLOYEE tjce
> > on tjce.JOB_CARD_ID = tjc.JOB_CARD_ID
> >
> > LEFT OUTER JOIN TBL_CLIENT tc
> > ON (tc.CLIENT_ID = tjc.CLIENT_ID)
> > LEFT OUTER JOIN TBL_INSURER ti
> > ON (ti.INSURER_ID = tjc.INSURER_ID)
> > ORDER BY
> > tjc.JOB_CARD_ID DESC <--- make sure you have a DESC index on
> job_card_id
> >
> > ./heLen
> >
>
>

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64