|
tinac99
Starting Member
1 Posts |
 Posted - 12/17/2012 : 13:33:23
|
Hi,
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:
select pj.projectno,
pj.OpenCaseFlag,
pj.DateTimeReported,
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:
select
pj.projectno,
pj.OpenCaseFlag,
pj.DateTimeReported,
v.victimId,
pv.Lastname,
pv.Firstname,
s.suspectId,
ps.LastName,
ps.FirstName,
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.
Thanks,
Christina |
|
subqueries
