Is it a complex sql query?

[stockblaster]

Hello

I am using stored procedure with sql 2005 (with Visual studio 2005)

I have two tables .. TABLE1 And TABLE2

From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so

i did:
SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc

Now what i am trying to do is take the 4 row results (4 OrderID's) i
got from
TABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for a
specific
userID i get by INPUT varible (@UserId)..

What i want to return is only which OrderID'S existed in TABLE2 for the

specific user.

If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i will
return only 2 OrderID's (so i can do my output in visual studio 2005
using the reader())

I would appreciate this if anyone knows how to do this sql query , is
it possible to do this in 1 query? i want to put it in a stored
procedure.

[stockblaster]

I tried to use this query-
SELECT TOP 4 OrderID FROM TABLE1 WHERE exists (SELECT * From TABLE2
where @UserId=TABLE2.UserID)

But this query shows me the all 4 OrderID's if it finds the USERID in
TABLE2..

What i want to return is only which OrderID'S existes in TABLE2 for the
specific user.

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002

I want it to return only "2" if the INPUT Parameter of @UserID is 1002

[stockblaster]

Another example..

if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002
I want it to return only "2" and "3" if the INPUT Parameter of @UserID
is 1002

[John Bell]

Hi

Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a usable form

You can use something like:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Or (better!)

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @UserId=T2.UserId

Check out the topics "Using Joins" and "Join Fundamentals" in books online

John

<st**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Another example..

if i have in TABLE1:
OrderID
1
2
3
4
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002
I want it to return only "2" and "3" if the INPUT Parameter of @UserID
is 1002

[stockblaster]

Excellent!

Thanks a lot John, it seems to work just fine.. i used the second
example.

[stockblaster]

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

[stockblaster]

I tried to find this in the documents on the web ..I couldn't find a
way of how to perform this only for the TOP 4 of TABLE1.

Now what happenes:
[e-mail]
Jan 15, 10:01 am show options

Newsgroups: comp.databases.ms-sqlserver
From: [e-mail] - Find messages by this author
Date: 15 Jan 2006 00:01:50 -0800
Local: Sun, Jan 15 2006 10:01 am
Subject: Re: Is it a complex sql query?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Another example..

TABLE1:
OrderID
1
2
3
4
5
6
7
8
9
10
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

It will return me: 4 5 6 7 (the top 4 of what it finds)
i use now:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
Any ideas?

[John Bell]

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

<st**********@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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 ..

[stockblaster]

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 :(

[Hugo Kornelis]

On 15 Jan 2006 10:09:18 -0800, st**********@gmail.com wrote:

Hello

I am sorry, i didn't explain my self what i wanted to acchive exactly.

(snip)

Hi Stockblaster,

That's exactly the reason why John suggested you to read the information
at www.aspfaq.com/5006 in his first post to you - posting CREATE TABLE
and INSERT statements and expected output is a much better way to
explain your needs than pure narrative.

If I understand your requirements correctly, then maybe something like
this will work:

SELECT t1.OrderId
FROM (SELECT TOP 4 OrderID
FROM Table1
ORDER BY OrderID DESC) AS t1
LEFT JOIN Table2 AS t2
ON t2.OrderId = t1.OrderId
AND t2.UserId = @UserId
WHERE t2.OrderID IS NULL

(untested - see www.aspfafq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[stockblaster]

Hello Hugo..

Very nice! i believe this 1 did the work..

Thanks a lot.. this 1 was stiff.

Is there any good book you can recommend me for sql 2005 (with SQL
Server Management Studio) ... How to upload to a shared web hosting,
when to use relationships, some sql querys examples? all the basics.

[John Bell]

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

st**********@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 :(

[John Bell]

Hi

I don't think you will get a single books to cover all these topics,
and you will have to be careful of books based on the pre-release
versions. You may want to check out THe Microsoft SQL Server 2005
Administrator's Pocket Consultant ISDN 0735621071 for configuration
information, and there is always books online. Also check out SQL
Server magazine which has many articles that will be benificial
http://www.windowsitpro.com/SQLServer/

John

[Hugo Kornelis]

On 15 Jan 2006 15:27:13 -0800, st**********@gmail.com wrote:

Hello Hugo..

Very nice! i believe this 1 did the work..

Thanks a lot.. this 1 was stiff.

Is there any good book you can recommend me for sql 2005 (with SQL
Server Management Studio) ... How to upload to a shared web hosting,
when to use relationships, some sql querys examples? all the basics.

Hi Stockblaster,

I'm sorry, I can't help you here.

Personally, I'm going to wait for Inside SQL Server 2005, that Kalen
Delaney is (hopefully) working on right now. However, the "Inside..."
series are "how does it work" kind of books; you seem to be seeking the
"how do I operate it" kind of books.

--
Hugo Kornelis, SQL Server MVP

[stockblaster]

Hello John.

I am not sure i understand, do you mean if table1 contains only two
records? so the top 4 will not work?

[John Bell]

Hi

Sorry for the delayed reply, this one slipped through the net.

My question was related to

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

Do you actually want 3,4,5 as this is less than the maximum for 1001 which
is already 7?

John
<st**********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hello John.

I am not sure i understand, do you mean if table1 contains only two
records? so the top 4 will not work?