开发者

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.

  1. There's no ORDER BY clause for your T1 SELECT, so how do you know which TOP 250 is being returned?

  2. 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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜