Subquery in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
merkava
Forum Newbie
Posts: 3
Joined: Fri Jan 30, 2004 12:52 am

Subquery in MySQL

Post 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.
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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
merkava
Forum Newbie
Posts: 3
Joined: Fri Jan 30, 2004 12:52 am

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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