MySQL Select Query Question

[hoihtah]

ok, so i'm trying to query from a mysql database tables.
any help would be greatly appreciated.

table : buyer
-------------------------------
buyer_id | name
------------------------------
1 | john
2 | sally


table : fruit
------------------------------
fruit_id | fruit
------------------------------
1 | apple
2 | orange


table : purchase
-----------------------------
purchase_id | buyer_id | fruit_id
1 | 1 | 1
2 | 1 | 1
3 | 1 | 2
3 | 2 | 1

i'm trying to have one query that'll output something like this

name | fruit
john | 2x apple, 1x orange
sally | 1x orange

i'd normally use two queries and use php to script it...
but there's got to be a way to do this within mysql with one query.

anyone?

 

[imported_LoKe]

If you're selecting from two different tables, I believe you'd have to use two seperate queries.

 

[hoihtah]

Originally posted by: LoKe
If you're selecting from two different tables, I believe you'd have to use two seperate queries.

you can draw queries from two different tables... or three for this matter

SELECT purchase_id, buyer.name, fruit.fruit
FROM buyer, fruit, purchase
WHERE buyer.buyer_id=purchase.buyer_id AND fruit.fruit_id=purchase.fruit_id

if i do the query above it will list four lines

1, john, apple
2, john, apple
3, john, orange
4, sally, apple

i am wondering how i an merge two lines (1&2) into one.

how do i set up the query so that if same buyer_id appears multiple times, merge and concatenate into one line?

 

[mooncancook]

this is the closest I can think of (in MS SQL syntax)

SELECT buyer.name, fruit.fruit, COUNT(purchase.buyerID) as PurchaseNo
FROM purchase JOIN buyer on buyers.buyer_ID=fruit.buyer_ID
JOIN fruit ON fruit.fruit_ID=purchase.fruit_ID
GROUP BY buyer.name, fruit.fruit

 

[notfred]

Originally posted by: LoKe
If you're selecting from two different tables, I believe you'd have to use two seperate queries.

Don't give any more SQL advice

I might be able to give you a working query, but I'm not going to bother setting upthe whole database to do it.

Give me access to your database and I'll play with it.

 

[Thyme]

Try this post: http://forums.oracle.com/forums/message.jspa?messageID=762693

 

[imported_LoKe]

Originally posted by: notfred

Originally posted by: LoKe
If you're selecting from two different tables, I believe you'd have to use two seperate queries.

Don't give any more SQL advice

I might be able to give you a working query, but I'm not going to bother setting upthe whole database to do it.

Give me access to your database and I'll play with it.

SQL is not one of my strong points.

 

[stndn]

SELECT buyer.name AS name, fruit.fruit AS fruit, COUNT(fruit.fruit_id) AS purchase
FROM purchase
LEFT JOIN buyer USING (buyer_id)
LEFT JOIN fruit USING (fruit_id)
GROUP BY name, fruit


Output:
name fruit purchase
john apple 2
john orange 1
sally apple 1

 

[troytime]

SELECT * FROM purchase
LEFT JOIN fruit on fruit.fruit_id = purchase.fruid_id
LEFT JOIN buyer on buyer.buyer_id = purchase.buyer_id

 

[Thyme]

That's not what he wants.

 

[hoihtah]

i tried to follow the link that you gave...
but it's a bit confusing.

oracle syntex is a little bit different from mysql.

thanks a bunch though, for pointing me in the right direction.
i'm still digging... to see how i can get this set up.

 

[Thyme]

It's fairly complicated to do in SQL, it seems. You might want to do as suggeseted to get a 1NF table and use script from there.

 

[hoihtah]

so far, i've gotten this far.

SELECT purchase_id, buyer.name,
(SELECT GROUP_CONCAT(DISTINCT fruit.fruit ORDER BY fruit.fruit SEPARATOR ', ')
AS fruit
FROM buyer, fruit, purchase
WHERE buyer.buyer_id=purchase.buyer_id AND fruit.fruit_id=purchase.fruit_id

now, i'm looking for ways to concat the quantity in.

 

[Ktulu]

why don't you use some inline views

 

[hoihtah]

Originally posted by: Ktulu
why don't you use some inline views

excuse my ignorance...
but what's inline view?