How to Join 3 Tables and Count Instances of Data

Transact-SQL
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