Posted - 12/17/2012 : 13:33:23
I'm supposed to display a project, with its list of suspects and its list of victims.
I'm constructing a sql with possibly 2 subqueries:
suspects = (select p.lastname,p.firstname from suspect s inner join person p on s.personid = p.personid and s.projectno = pj.projectno)
suspects = (select p.lastname,p.firstname from victim v inner join person p on v.personid = p.personid and v.projectno = pj.projectno)
from projects pj
Can anyone give an insight on the performance of this query?
I thought of doing all outer joins:
from dbo.Project pj
left outer join (dbo.Victim v inner join dbo.person pv on v.personid = pv.personid) on t.ProjectNo = v.ProjectNo
left outer join (dbo.Suspect s inner join dbo.person ps on s.personid = ps.personid) on t.ProjectNo = s.ProjectNo
But the all suspects are listed on every victim instead of the project, which makes the count wrong. Also, it will have an error on paging(page size) and when I click prev/next on the result set on the client end.
If anybody has a more effiecient way of doing this, please let me know.