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!