Dan Dan - 6 months ago 12
MySQL Question

SQL - Select names associated with IDs

I'm having trouble writing an SQL statement to display a table showing names instead of IDs. I have the following 3 tables:

Item table

id price name stock
----------------------
1 0.99 Nuts 43


Customer table

id name
----------------------
1000 Bill


Purchase Table

id itemID Quantity custID
--------------------------------
100 1 1 1000


Essentially, I want a table that has the headings: Item Name, Item Price, Purchase Quantity and Customer Name

Answer

That's a pretty basic JOIN , you should read the SQL Tutorial about it

SELECT i.name,i.price,sum(p.quantity),c.name
FROM Item i
INNER JOIN Purchase p
 ON(i.id = p.itemID)
INNER JOIN Customer c
 ON(c.id = p.custID)
GROUP BY i.name,c.name