SQL Left Join two tables into one

Question

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

Answer 1

You can resolve this issue using the following query:

SELECT T1.Name, Count(Tbl_Computer.Prod_ID)
FROM Tbl_Consumer T1 LEFT JOIN 
     (SELECT T2.Prod_ID, T2.Con_ID 
      FROM Tb_Requests T2 
           INNER JOIN Tb_Product T3 
           ON T2.Prod_ID = T3.Prod_ID 
      WHERE T3.Name = 'Computer') AS Tbl_Computer
      ON T1.Con_ID = Tbl_Computer.Con_ID 
 WHERE T1.City = 'Madison'