Page 1 of 1

Subquery in MySQL

Posted: Fri Jan 30, 2004 12:52 am
by merkava
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.

Posted: Mon Feb 02, 2004 3:17 pm
by xisle
just take it one step further and join three tables..
something like this..

Code: Select all

SELECT item.ItemID, item.Title, MIN(item_qty.Cost) as cost 
FROM item_cat, item, item_qty
WHERE item_cat.CatID='1' 
&& item_cat.ItemID=item.ItemID 
&& item_cat.ItemID=item_qty.ItemID

Posted: Mon Feb 02, 2004 11:35 pm
by merkava
Hi,
thanks for you response.
I found myself that this query works for me:

SELECT item.ItemID, item.Title, MIN(item_qty.Cost) AS CostMin
FROM item_cat, item LEFT JOIN item_qty
USING (ItemID)
WHERE item_cat.ItemID=item.ItemID
AND (item_cat.CatID='1')
GROUP BY ItemID

Well, yours seems easier!
thx.

Posted: Tue Feb 03, 2004 12:29 am
by timvw
There is no need to have the
item_cat.ItemID=item.itemID in your where clause since your "join using" already requires that.