MySQL:显示 3 个表中的所有行

Question

我正在尝试显示 3 个表中的所有行。

我需要显示完整的客户列表。每个客户可以有 0 个、1 个或多个提交。每次提交可以有 0,1 或更多产品。

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

所以:

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.

我似乎无法显示所有记录。下面的 SQL 将显示所有客户，但不会显示多个提交行或多个产品行。

当然，有些客户会有空白的提交和产品。并且一些客户提交的内容不会有产品。

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;

任何帮助将不胜感激。谢谢。

Answer 1

如果你只想获取所有记录，请试试这个；)

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;