Subquery in MySQL
Posted: Fri Jan 30, 2004 12:52 am
Hi,
I am new with MySQL/PHP and I can't do something that seems easy.
Ok, here is my pb.
I have a query with a join that returns :
itemID | Title
001 | xxxx
003 | zzzzz
SELECT item.ItemID, item.Title
FROM item_cat, item
WHERE item_cat.CatID='1'
AND item_cat.ItemID=item.ItemID
I have another query with a group command that retruns:
itemID | Cost
001 | 10
002 | 12
003 | 30
004 | 40
SELECT ItemID, MIN(Cost)
FROM item_qty
GROUP BY ItemID
I want the final result to be:
itemID | Title | Cost
001 | xxxx | 10
003 | zzzzz | 30
My version of MySQL does not support subquery. I'm in 4.0.x.
How can i do that? Do i need to create a temp table?!
Thanks a lot.
I am new with MySQL/PHP and I can't do something that seems easy.
Ok, here is my pb.
I have a query with a join that returns :
itemID | Title
001 | xxxx
003 | zzzzz
SELECT item.ItemID, item.Title
FROM item_cat, item
WHERE item_cat.CatID='1'
AND item_cat.ItemID=item.ItemID
I have another query with a group command that retruns:
itemID | Cost
001 | 10
002 | 12
003 | 30
004 | 40
SELECT ItemID, MIN(Cost)
FROM item_qty
GROUP BY ItemID
I want the final result to be:
itemID | Title | Cost
001 | xxxx | 10
003 | zzzzz | 30
My version of MySQL does not support subquery. I'm in 4.0.x.
How can i do that? Do i need to create a temp table?!
Thanks a lot.