This question is more specific than general. I understand SQL a good amount, and understand JOINs and UNIONs etc, but I am having a real problem with a query I need. I have three tables:

ITEM: ITEM_ID
      ITEM_NAME
WISH: ITEM_ID
      CUST_ID
SELL: ITEM_ID
      CUST_ID

What I need from these tables is a result that shows the following information: Item ID, Item Name, the number of entries in WISH for each item, as well as the number of entries in SELL for each item. I would also prefer that it only show items that have an entry in either WISH or SELL.

Most preferably I would like it in one all-encompassing query because I use a jQuery plug-in to put it all in an HTML table, which allows it to be searched and ordered, but only if it is in a single query.

I have tried various JOINS, UNIONS etc, but they don't return the needed results and whenever I use COUNT it only returns one result - an explanation of that would be great if you know why it does that, just for the sake of education.

Thank you for any help!

Select Item_ID , Item_name ,
(Select Count(*) from Wish W where W.Item_id= I.Item_Id) as Wish_Count,
(Select Count(*) from Sell S where S.Item_id= I.Item_Id) as Sell_Count
From Item I
where 
I.Item_Id in ( Select distinct Item_Id from Wish) OR  
I.Item_Id in ( Select distinct Item_Id from SELL)
Select Item_ID , Item_name ,
(Select Count(*) from Wish W where W.Item_id= I.Item_Id) as Wish_Count,
(Select Count(*) from Sell S where S.Item_id= I.Item_Id) as Sell_Count
From Item I
where 
I.Item_Id in ( Select distinct Item_Id from Wish) OR  
I.Item_Id in ( Select distinct Item_Id from SELL)

Thank you very much, that worked perfectly. For some reason I was thinking that MySQL did not support putting a sub query in the fields part of the query, but you have proved my thought incorrect.


Thanks again!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.