Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tinac99
Starting Member
1 Post |
 Posted - 2012-12-17 : 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 pjCan 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 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-17 : 13:48:46
|
| Post you query with sample and expected output so that people will help you. |
 |
|
|
|
|
|
|
|
subqueries