In general I've found joins to be faster than subqueries with count or
exists. I guess joins are easier to optimize than the other kinds. (?)


In theory, an exists subquery should be faster than a count subquery,
because it can stop when the first subrecord is found. With count, it
has to count them all.

Anyway, doesn't this work:

SELECT
[snip irrelevant part]
case when exists (
SELECT 1
FROM TBL_JOB_CARD_EMPLOYEE
WHERE TBL_JOB_CARD.JOB_CARD_ID
= TBL_JOB_CARD_EMPLOYEE.JOB_CARD_ID
) then 'Yes' else 'No' end AS HAS_EMPLOYEES,
TBL_JOB_CARD.AMOUNT_DUE,
TBL_JOB_CARD.INVOICE_PRICE_INCL,
TBL_JOB_CARD.VAT
FROM TBL_JOB_CARD
LEFT OUTER JOIN TBL_CLIENT
ON (TBL_JOB_CARD.CLIENT_ID
= TBL_CLIENT.CLIENT_ID)
LEFT OUTER JOIN TBL_INSURER
ON (TBL_JOB_CARD.INSURER_ID
= TBL_INSURER.INSURER_ID)
ORDER BY JOB_CARD_ID DESC

Kjell

dinol.softedge 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
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Kjell Rilbe <kjell.rilbe@...>
> wrote:
> >
> > As far as I can see, you're trying to actually select an employee count
> > by using a join with a 1-M realtionship. How could that be returned
> with
> > an exists query?
> >
> > Either you're not telling us what you actually want, or I'm missing
> > something.
> >
> > Kjell
> >
> > dinol.softedge wrote:
> > >
> > >
> > > Hi
> > >
> > > I am trying to optimize the following count query by changing it to an
> > > exists query. I have tried various exists statements but I cannot
> > > seem to get it right. Any help would be greatly appreciated
> > >
> > > The count subquery is near the bottom
> > >
> > > SELECT
> > [snip irrelevant part]
> > > (SELECT COUNT(EMPLOYEE_ID) FROM TBL_JOB_CARD_EMPLOYEE WHERE
> > > TBL_JOB_CARD.JOB_CARD_ID = TBL_JOB_CARD_EMPLOYEE.JOB_CARD_ID) AS
> > > EMPLOYEE_COUNT,
> > > TBL_JOB_CARD.AMOUNT_DUE,
> > > TBL_JOB_CARD.INVOICE_PRICE_INCL,
> > > TBL_JOB_CARD.VAT
> > > FROM
> > > TBL_JOB_CARD
> > > LEFT OUTER JOIN TBL_CLIENT ON (TBL_JOB_CARD.CLIENT_ID =
> > > TBL_CLIENT.CLIENT_ID)
> > > LEFT OUTER JOIN TBL_INSURER ON (TBL_JOB_CARD.INSURER_ID =
> > > TBL_INSURER.INSURER_ID)
> > > ORDER BY
> > > JOB_CARD_ID DESC
> > >
> > >
> >
> > --
> > --------------------------------------
> > Kjell Rilbe
> > DataDIA AB
> > E-post: kjell@...
> > Telefon: 08-761 06 55
> > Mobil: 0733-44 24 64
> >
>
>

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