Need assistance with Left Outer join?
SELECT ID
(
Select ID from Table1
where Table1.ID=@ID
)T1
Left Outer join
(
Select top 1 Table2.ID from Table2 join Table3 on table3.I开发者_开发百科D=Table2.ID
order by Table2.ID DESC
)T2 on T2.ID=T1.ID
This is just an example of the actual stored procedure I have with me, the problem which I am facing is that I'm unable to retrieve the values from T2 it just returns as NULL but when I change it Top 5 i am able to retrieve the values. Is this Join correct, is it necessary to have where part inside left outer join in order to retrieve the values?
If you are using TOP you need to decide on all selects (all three) how your data should be ordered so you can have some control on which values you are getting back, and maybe also being more specific on what you filter.
A couple of observations.
There's no ORDER BY clause for your T1 SELECT, so how do you know which TOP 250 is being returned?
If T2 returns NULL, then there is no match between T1 and T2, possibly due to my first point?
What exactly are you trying to accomplish?
You could try something like this:
SELECT
TOP 250 Table1.ID
FROM
Table1
LEFT OUTER JOIN
Table2 ON Table2.ID = Table1.ID
LEFT OUTER JOIN
Table3 ON Table3.ID = Table2.ID
WHERE
Table1.ID = @ID
ORDER BY
Table1.ID DESC
精彩评论