CODEWITHSUNDEEP
sqljoin
Phoenix
Phoenix

Reputation: 13

SQL: How to show all rows from one table that are not all listed in the 2nd table joined

I am having difficulty getting all the rows to show from the 'Customer' table, as they are not all listed in the 'SalesOrder' table I have joined.

How do I get all the rows to show from the Customer table even if they have a 0 value? Thank you for any suggestions :)

SELECT Customer.CID, Customer.Name, 
COUNT (SalesOrder.CID) AS NbrOrders, 
SUM (FullPrice) AS [Total Value of Items]
FROM (Customer
  INNER JOIN SalesOrder
  ON Customer.CID=SalesOrder.CID)
GROUP BY Customer.CID, Customer.Name;

Upvotes: 0

Views: 46

Answers (2)

shree.pat18
shree.pat18

Reputation: 21757

Use a left join. This will show values from second table which dont have corresponding data in first table.

Upvotes: 0

PravinS
PravinS

Reputation: 2584

use LEFT JOIN instead of INNER JOIN

SELECT Customer.CID, Customer.Name, 
COUNT (SalesOrder.CID) AS NbrOrders, 
SUM (FullPrice) AS [Total Value of Items]
FROM Customer
  LEFT JOIN SalesOrder
  ON Customer.CID=SalesOrder.CID
GROUP BY Customer.CID, Customer.Name;

Upvotes: 1

Related Questions