Click to See Complete Forum and Search --> : MySQL query - not sure how to do this one


intrivious
07-29-2006, 11:24 AM
here are my tables (simplified):

users:
uID | name
-----------
1 | Chad
2 | Carla

items:
iID | item
-----------
1 | baseball
2 | soccer ball
3 | basketball

purchases:
pID | uID | iID
----------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 3

The question:
How do I make a query resulting in the 'users.name' and 'number of purchases by that user'

By these tables it should look like:
chad | 2
carla | 1

What query can do that for me?

Thanks in advance,
chad

russell
07-29-2006, 11:51 AM
SELECT u.name, count(p.uID) as numPurchases
FROM users u
INNER JOIN
purchases p
On p.uID = u.uID
GROUP BY
u.name

intrivious
07-30-2006, 11:21 PM
Great! That works perfect, thanks.

I do have another little tweak I need to make.

Say I wanted to display:
user.name, 'number of purchases', items.item

Obviosuly, I would add 'items.item' to the SELECT segment of the SQL, but doing this will return:

chad | 1 | baseball
chad | 1 | soccer ball
carla | 1 | basketball

Is there anyway I can have a query that will return:

chad | 2 | baseball
chad | 2 | soccer ball
carla | 1 | basketball

I need the query result to maintain the number of 'number of total purchases' not the, 'number of purchases per record returned'

I hope this made sense, thanks in advance,
chad