>Hi all
>
>I have a piece of SQL that really needs to be performed quicker, I have 2 tables:
>
>Person
>Referral
>
>Person has a date of birth and I am fetching all persons who were under 18 on their first referral, here is the SQL:
>
>
>select *;
> from person p
> inner join referral r on p.cl_ref = r.cl_ref and !empty(r.re_date);
> where gomonth(p.cl_dob, 216) > (select min(rr.re_date) from referral rr;
> where rr.cl_ref = p.cl_ref and !empty(rr.re_date))
>
>
>According to Fox none of this is optimised, both cl_ref fields on each table are indexed.
>
>Can anyone suggest an alternative that may be quicker - or is there anything else I could do to speed up the query?
>
>Thanks
>Kev
How about:
select *;
from person
inner join (SELECT cl_ref, MIN(re_date) AS Re_Date;
FROM referral;
GROUP BY cl_ref;
WHERE NOT EMPTY(re_date)) Rr;
ON person.cl_ref = Rr.cl_ref
WHERE p.cl_dob <= GOMONTH(Rr.Re_Date,-216)
But it depends what indexes you have in both tables.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.