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, 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
>