CODEWITHSUNDEEP
sqlcountforeign-keys
Isaac Perez
Isaac Perez

Reputation: 590

SQL query involving foreign keys

I was reading through the SQL tutorial at http://www.w3schools.com/sql/sql_foreignkey.asp and was wondering how a query would be constructed if we had multiple tables referencing the same primary key. For example, in the aforementioned link, what if we had an Orders2, Orders3, and Orders4 table that all had similar functionality(of the table 'Orders') such that they have a column named P_Id.

How could I create a query that lists the number of times a customer is listed in the tables 'Orders', 'Orders2', 'Orders3', and 'Orders4', along with the corresponding customer's name?

Upvotes: 0

Views: 183

Answers (1)

PinnyM
PinnyM

Reputation: 35533

Join each table using a LEFT JOIN in case no orders exist for that person in some of these tables:

SELECT p.P_Id, p.LastName, p.FirstName, 
       COUNT(a.P_Id) AS OrdersCount, 
       COUNT(b.P_Id) AS Orders2Count, 
       COUNT(c.P_Id) AS Orders3Count, 
       COUNT(d.P_Id) AS Orders4Count
FROM Persons p
LEFT JOIN Orders a ON p.P_Id = a.P_Id
LEFT JOIN Orders2 b ON p.P_Id = b.P_Id
LEFT JOIN Orders3 c ON p.P_Id = c.P_Id
LEFT JOIN Orders4 d ON p.P_Id = d.P_Id
GROUP BY p.P_Id, p.LastName, p.FirstName

Upvotes: 3

Related Questions