selecting records on basis of condition

[chintan85]

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

[r035198x]

Erm

select itemID from orderitems

?

[Rabbit]

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.

[rekedtechie]

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

SELECT ItemID,OrderID FROM your_table_name WHERE OrderID = 889

[Rabbit]

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.

[chintan85]

Rabbit is correct. I am trying to write a subquery here..but i m not able to generate one.. any help again is appreciated

[Rabbit]

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.

[chintan85]

Can you plz correct me..

select orderID, ItemID from orderitems where orderID in (select orderID where itemID =889)

or
select orderID, ItemID from orderitems inner join select orderID from orderitems where itemID =889

Thanks

[Rabbit]

The subquery in your first one is missing the from clause.

select orderID, ItemID 
from orderitems 
where orderID in (
   select orderID 
   from orderitems
   where itemID =889
)

The second one is missing parentheses to indicate that it's a subquery. An alias for the subquery. And the fields to join on.

select orderID, ItemID 
from orderitems 
inner join (
   select orderID 
   from orderitems 
   where itemID =889
) x on orderitems.orderID = x.orderID

[chintan85]

Thank you very much...

[Rabbit]

You're welcome. I'm assuming this resolves your question?

[chintan85]

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

    select [B]orderitems.[/B]orderID, [B]orderitems.[/B]ItemID 
    from orderitems 
    inner join (
       select orderID 
       from orderitems 
       where itemID =889
    ) x on orderitems.orderID = x.orderID

Thanks