I have 3 Tables:

• Trips which contains TripRecordID and TripName
• TripRecords which contains TripRecordID, CustID, and other details for that trip record
• Customers which contains CustID and customer information

What I would like is a view that lists every single customer with a row for each trip that exists in the Trip table and a count of how many trip records exist for that customer for that trip (which could be 0) E.g.

If there are 2 trips and 4 customers, the view would list each customer twice, one for trip 1 and one for trip 2. In each row it would count how many times that customer has a record matching the trip specified. If we add another trip, each customer will get 3 rows and so on.

For example:

I'm struggling to write the query. This query returns every customer but not a line for each trip (since not all customers are on all trips):

SELECT c.CustID, t.TripID, COUNT (tr.TripID) AS TripCount
FROM dbo.Customers c
LEFT OUTER JOIN dbo.TripRecords tr ON tr.CustID = c.CustID
LEFT OUTER JOIN dbo.Trips t ON tr.TripID = t.TripID
GROUP BY c.CustID, t.TripID

CodePudding user response：

Build your query up from the data your want. So if you want a row per customer, per trip you need a CROSS JOIN which gives every combination. Then you can carry out your LEFT JOIN to see how many of each combination occured.

SELECT c.CustID, t.TripID, COUNT(tr.TripID) AS TripCount
FROM dbo.Customers c
CROSS JOIN dbo.Trips t
LEFT OUTER JOIN dbo.TripRecords tr ON tr.TripID = t.TripID AND tr.CustID = c.CustID
GROUP BY c.CustID, t.TripID