September 2, 2009 at 5:58 am
Hello,
I am having problems with constructing a left outer join to bring back the results I require.
I've pasted what I have done so far.
HomID is the foreign key between Table A and Table B.
My problem is that in Table B there could be more than one entry with the same 'HomID' and where the [Person Classification] column is equal to 'Victim' however, I only want to join on one of the rows.
At present the below query returns two rows if there are two rows in table B with the same HomID
SELECT HD.ID,
HD.Force,
HD.[Column A] A,
HD.[Column B] B,
PC.[Column C] C,
HD.D
FROM dbo.[Table A]HD
left outer join (select HomID,[Last Name(s)]
from dbo.[Table B]
where [Person Classification] = 'Victim') PC
on HD.ID = PC.HomID
where HD.Status = 'In Progress' AND HD.IsActive = 1
order by HD.CreatedOn desc
September 2, 2009 at 6:26 am
Apologies,
It appears that a similar question has been asked and I have found a solution.
September 4, 2009 at 2:14 am
marc.pollacchi (9/2/2009)
SELECT HD.ID,
HD.Force,
HD.[Column A] A,
HD.[Column B] B,
PC.[Column C] C,
HD.D
FROM dbo.[Table A]HD
left outer join (select HomID,[Last Name(s)]
from dbo.[Table B]
where [Person Classification] = 'Victim') PC
on HD.ID = PC.HomID
where HD.Status = 'In Progress' AND HD.IsActive = 1
order by HD.CreatedOn desc
Hi,
I have a doubt is this case.....
I think here the derived table PC reduces the cost of joining.
If I do not make derived table, joining directly with table B and placing the condition "[Person Classification] = 'Victim'" in where clause,
Is SQL server optimization itself can prioritize the operations like, doing [Person Classification] = 'Victim and then do the join ?
Thanks
Prileep
September 4, 2009 at 12:10 pm
You could try with and with out a derived table and see if you get a different query plan or execution time. But, it shouldn't make any difference to SQL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply