By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

selecting records on basis of condition

P: 33
Hi Guys,

Can you plz help me out with this query.

Using a single query, I want to find out the orderIDs that have itemID 889 and then list all the itemIDs for these orderIDs.

The table orderitems is like this way

orderID itemID
101 889
101 677
102 889
102 456
103 974


Output should be:

orderID itemID
101 889
101 677
102 889
102 456


Thanks..
Mar 19 '12 #1
Share this Question
Share on Google+
11 Replies


10K+
P: 13,264
Erm

Expand|Select|Wrap|Line Numbers
  1. select itemID from orderitems
?
Mar 19 '12 #2

Rabbit
Expert Mod 10K+
P: 12,366
Use a subquery to filter for those order ids that have an 889. Use a distinct if you're going to do it as a join, unless you're sure that there is no duplication on order id and item id.
Mar 19 '12 #3

P: 51
these can be done by implementing the WHERE statement. =)

SELECT ItemID,OrderID FROM your_table_name WHERE OrderID = 889
Mar 20 '12 #4

Rabbit
Expert Mod 10K+
P: 12,366
I think you two misunderstand the question. The goal is to find which order ids have item 889 and then return all items from those orders.
Mar 20 '12 #5

P: 33
Rabbit is correct. I am trying to write a subquery here..but i m not able to generate one.. any help again is appreciated
Mar 20 '12 #6

Rabbit
Expert Mod 10K+
P: 12,366
Refer to post #3. It tells you what kind of subquery is needed. Make an attempt and then post back here with what you attempted and the results.
Mar 20 '12 #7

P: 33
Can you plz correct me..

Expand|Select|Wrap|Line Numbers
  1. select orderID, ItemID from orderitems where orderID in (select orderID where itemID =889)
  2.  
  3. or
  4. select orderID, ItemID from orderitems inner join select orderID from orderitems where itemID =889
  5.  
Thanks
Mar 21 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
The subquery in your first one is missing the from clause.
Expand|Select|Wrap|Line Numbers
  1. select orderID, ItemID 
  2. from orderitems 
  3. where orderID in (
  4.    select orderID 
  5.    from orderitems
  6.    where itemID =889
  7. )
The second one is missing parentheses to indicate that it's a subquery. An alias for the subquery. And the fields to join on.
Expand|Select|Wrap|Line Numbers
  1. select orderID, ItemID 
  2. from orderitems 
  3. inner join (
  4.    select orderID 
  5.    from orderitems 
  6.    where itemID =889
  7. ) x on orderitems.orderID = x.orderID
Mar 21 '12 #9

P: 33
Thank you very much...
Mar 21 '12 #10

Rabbit
Expert Mod 10K+
P: 12,366
You're welcome. I'm assuming this resolves your question?
Mar 21 '12 #11

P: 33
Hi,

Just one change in one of the query above. the 1st one runs fine in 2nd one we need to add alias for the table orderitems in first part of query
Expand|Select|Wrap|Line Numbers
  1.     select orderitems.orderID, orderitems.ItemID 
  2.     from orderitems 
  3.     inner join (
  4.        select orderID 
  5.        from orderitems 
  6.        where itemID =889
  7.     ) x on orderitems.orderID = x.orderID
  8.  
  9.  
Thanks
Mar 22 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.