Home SQL Left Join two tables into one
Reply: 1

SQL Left Join two tables into one

Wisco Gold
1#
Wisco Gold Published in 2017-12-07 03:10:13Z

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

Hadi
2#
Hadi Reply to 2017-12-07 04:07:55Z

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'
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.298036 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO