Posted by John Bell on 01/15/06 14:22

Hi

I should have said that TOP without and ORDER BY clause is a bit
meaningless.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT EXISTS (SELECT * FROM TABLE2 t2
WHERE @UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
ORDER BY t1.OrderID

Will return you all rows OrderIds from Table1 where a row in Table2 does not
exist for that OrderId AND has a UserId of @UserId. With the ORDER BY means
1, 4, 5 and 6 are returned.

To do this using a JOIN, an OUTER JOIN is required.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID

John

<stockblaster@gmail.com> wrote in message
news:1137325768.003889.45140@g47g2000cwa.googlegroups.com...
> Hello again
>
> Finally i decieded to use SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE exists (SELECT * FROM TABLE2 t2
> WHERE @UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

>
> and modifed it to:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE NOT exists (SELECT * FROM TABLE2 t2
> WHERE @UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )
>


> (Notice the "NOT")
> Because i wanted it to return me the OrderID's (from the top 4 of
> course) that does not exist in TABLE2 ..
>
> I couldn't do it with the JOIN thingy even if i changed OrderId <>
> T2.OrderId ..
>

[Back to original message]