Here is a performance summary of the query

Query Time
------------------------------------------------
Prepare : 16
Execute : 3 978
Avg fetch time: 1.48 ms

Memory
------------------------------------------------
Current: 1 325 744
Max : 2 275 452
Buffers: 2 048

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 7 810 510

Plan:
------------------------------------------------
PLAN (TBL_JOB_CARD_EMPLOYEE NATURAL)
PLAN SORT (JOIN (JOIN (TBL_JOB_CARD NATURAL,TBL_CLIENT INDEX
(RDB$PRIMARY2)),TBL_INSURER INDEX (RDB$PRIMARY3)))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
| Table Name | Index | Non-Index | Updated | Deleted |
Inserted |
| | reads | reads | | |
|
+--------------------------+-------+-----------+---------+---------+----------+
| RDB$FIELDS| 100 | 0 | 0 | 0 |
0 |
| RDB$RELATION_FIELDS| 100 | 0 | 0 | 0 |
0 |
| RDB$RELATIONS| 6 | 0 | 0 | 0 |
0 |
| TBL_CLIENT| 2 689 | 0 | 0 | 0 |
0 |
| TBL_INSURER| 2 685 | 0 | 0 | 0 |
0 |
| TBL_JOB_CARD| 0 | 2 689 | 0 | 0 |
0 |
| TBL_JOB_CARD_EMPLOYEE| 0 | 3 837 677 | 0 | 0 |
0 |
+--------------------------+-------+-----------+---------+---------+----------+

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