Looks like you want to have an inner join to join p to branch, and branch to
supplierregion, as in:
select * from product p
left join supplier s on p.supplierid=s.supplierid
left join make m on p.makeid=m.makeid
left join model md on p.modelid=md.modelid
left join partgroup g on p.groupid=g.groupid
left join parttype t on p.typeid=t.typeid
left join supplierbranch b on p.branchid=b.branchid
inner join branch on p.branchid=branch.branchid
inner join supplierregion on branch.regionname = region.supplierregion
where p.makeid=3 and p.groupid=14 and p.typeid=510
and region.customerregion = "Auckland City" and region.hours <= 48
Nested selects are less efficient than inner joins because in most cases the
db engine has to run 2 queries, (1) the inner query, (2) the outer. The
outer is run for as many times as the number of records returned by the
inner query. With inner joins however, most query engines can optimise the
query to use composite indices, resulting in the outer query being executed
only once. Does this make sense?
50 records certainly is not a lot and the above query ought to take less
than a second to run on a good machine. The other thing you ought to do if
the query is slow is to look at your indices, in particular, make sure you
have the following indices:
p.makeid, p.groupid=14, p.typeid, p.branchid (concatenated/composite index)
branch.regionname
region.customerregion, region.hours (concatenated/composite index)
Also need indices on all keys used in tables that you left outer join.
Regards,
Dennis.
> select * from product p
> left join supplier s on p.supplierid=s.supplierid
> left join make m on p.makeid=m.makeid
> left join model md on p.modelid=md.modelid
> left join partgroup g on p.groupid=g.groupid
> left join parttype t on p.typeid=t.typeid
> left join supplierbranch b on p.branchid=b.branchid
> where p.makeid=3 and p.groupid=14 and p.typeid=510
> and p.branchid in (
> select branchid from branch
> where regionname in (
> select
> supplierregion from
> region
> where customerregion =
> "Auckland City" and hours <= 48
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
