Posted by John Bell on 11/17/64 11:37

Hi

I am not sure if my interpretation is the same as Hugos!
If all 4 rows returned are below the maximum what should happen?

SELECT TOP 4 t1a.OrderID
FROM TABLE1 t1a
LEFT JOIN TABLE2 t2a ON t1.OrderId = t2a.OrderId AND @UserId =
t2a.UserId
WHERE t2.OrderID IS NULL
AND t1a.OrderID > ( SELECT MAX(t1b.OrderID) FROM TABLE1 t1b
JOIN TABLE2 t2b ON t1b.OrderId = t2b.OrderId AND @UserId = t2b.UserId )

ORDER BY t1a.OrderID DESC

John

stockblaster@gmail.com wrote:
> Hello
>
> I am sorry, i didn't explain my self what i wanted to acchive exactly.
> For this query:
> 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 DESC
>
>
>
> There is a problem with that..
> For example:
> Table1:
> OrderID
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> Table2:
> OrderID UserID
> 6 1001
> 7 1001
> 3 1002
> 4 1002
> the result will be:
> for user 1001
> 8
> 5
> 4
> 3
>
>
> I only need to get 8 and 5 which are the two orderID's the user didn't
> have from the top 4 in table 1 ..
>
> can't figure that out :(

• Next in forum: Re: Is it a complex sql query?
• Prev in forum: Re: question on inserting a record on sql server with identity column as key
• Thread view: Re: Is it a complex sql query?

[Reply to this message]