List consumer names from Madison and number of computer requests for each (0 if no requests)
Right now the following query says that it can not be bound, and I assume it's because I am trying to LEFT JOIN two tables to Tb_Requests. I don't know any other way around this.
SELECT Tb_Consumer.Name, COUNT(tb_requests.Prod_ID) 'Number of Requests'
FROM Tb_Consumer, Tb_Product
LEFT JOIN Tb_Requests
ON Tb_Consumer.Con_ID = Tb_Requests.Con_ID
WHERE Tb_Consumer.City = 'Madison'
AND Tb_Product.Name = 'Computer'
GROUP BY Tb_Consumer.Name
I have the following tables:
Tb_Consumer - Name, City, Con_ID
Tb_Supplier - Name, City, Supp_ID
Tb_Offers - Name, Quantity, Prod_ID, Supp_ID
Tb_Product - Name, Prod_ID
Tb_Requests - Name, Quantity, Prod_ID, Con_ID
Tb_Transactions - Tran_ID, Supp_ID, Con_ID, Quantity, Price