How to Join 3 Tables and Count Instances of Data

Transact-SQL
1

Hi everyone, 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:

CustID-TripID-TripCount
1-1-2
1-2-3
2-1-2
2-2-1
3-1-0
3-2-0
4-1-1
4-2-0

I'm struggling to format the Query. This query gets me 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

2

If you really want each customer for each trip you could use CROSS JOIN

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

Joins (SQL Server) - SQL Server | Microsoft Docs

3

How about:

SELECT *,
	ISNULL((select count(*) from dbo.TripRecords tr WHERE tr.CustID = c.CustID AND tr.TripID = T.TripID),0) as TripCount
FROM dbo.Customers c
CROSS JOIN dbo.Trips t
4

Thank you both, Cross Join was the tool missing from my toolbox! Thank you so much!