CODEWITHSUNDEEP
mysqljoin
nads
nads

Reputation: 517

MySQL: displayed all rows from 3 tables

I'm trying to display all rows from 3 tables.

I need to display the complete customer list. Each customer can have 0, 1 or more submissions. Each submission can have 0,1 or more products.

CustomerID | submissionID | submissionProductID
001        | s001         | sp001 
001        | s002         | sp002 
002        | s003         | sp003     
002        | s003         | sp004      
002        | s003         | sp005 
003        | ---          | ---
004        | s005         | ---

so:

customer 1 has 2 submissions, 1 product in each.
customer 2 has 1 submission containing 3 products.
customer 3 has no submission.
customer 4 has 1 submission but no pruducts.

I can't seem to display ALL records. The SQL below will display all the customers, but not multiple submisison rows, or multiple product rows.

And of course, some customer will have a blank submission and product. And some customer submissions will not have a product.

SELECT c.CustomerID as 'CustID', s.submissionID, sp.submissionProductID
FROM tblCustomer c
LEFT JOIN tblSubmission s on c.CustomerID = s.customerID
LEFT JOIN tblSubmissionProducts sp on s.submissionID  = sp.submissionID
GROUP BY c.CustomerID
ORDER BY c.CustomerID;

Any help will be appreciated. Thanks.

Upvotes: 1

Views: 39

Answers (1)

Blank
Blank

Reputation: 12378

If you just want to get all records, please try this;)

SELECT c.CustomerID as 'CustID', s.submissionID, sp.submissionProductID
FROM tblCustomer c
LEFT JOIN tblSubmission s on c.CustomerID = s.customerID
LEFT JOIN tblSubmissionProducts sp on s.submissionID  = sp.submissionID
ORDER BY c.CustomerID;

Upvotes: 3

Related Questions