Dan Dan - 1 year ago 62
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


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