Hello Gary,
Depends on way too many things, but assuming
- You are concerned about cardinality of wd Vs SJ
- SJ.sj_id is unique
- there are necessary indexes in place
...
Then I'd recommend subselect in SELECT clause, like:
select sj.*, sr.*, (select count(sj_id) from work_documents wd where wd.sj_id=SJ.sj_id) doc_count from
service_jobs sj
left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id;
(Note, haven't verified syntax)
Maybe if you could send explain plans and index info...
I have the following select:
select sj.*, sr.*, wd.doc_count from
service_jobs sj
left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id
left outer join (select sj_id, count(sj_id) as doc_count from work_documents wd group by sj_id) wd on wd.wd_sj_id = sj.sj_id;
While the select works, I can't help thinking that the last join is expensive. Is there a cleaner (quicker) method of doing this?
Gary