Malork Malork - 3 months ago 9
MySQL Question

Select things from three diferent tables with inner join

Well, I have three tables:

Cart:

ID | Product | Quantity
1 S1FG 3
1 C0K0 1
1 SLM1 6
2 S1FG 2
2 S94A 2


Products:

Code | Price
S1FG 10.00
C0K0 8.00
SLM1 19.50
S94A 2.00


And Users:

ID | Nickname
1 Mark
2 Steve


I want this:

Nickname | Products | Total
Mark 10 155.00
Steve 4 24.00


In words, I want a list of users that has something in Cart, a count of their items and a sum of the prices of their items.

I tried this:

SELECT DISTINCT b.Nickname, SUM(a.Quantity) as Products, SUM(a.Quantity*c.Price) as Total
FROM Cart a
INNER JOIN Users b ON (a.ID = b.ID)
INNER JOIN Products c ON (a.Product = c.Code)


But it didn't work...

What can I do?

Answer

You need to GROUP BY the nickname or you'll just get a single line:

SELECT DISTINCT b.Nickname, SUM(a.Quantity) as Products, SUM(a.Quantity*c.Price) as Total 
FROM Cart a 
INNER JOIN Users b ON (a.ID = b.ID) 
INNER JOIN Products c ON (a.Product = c.Code)
GROUP BY b.Nickname
Comments