mbSanDiego (3/21/2015)
I had to add each column being selected into a Group by to make it work, but I do not know why.SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor
If Car.CarId is PK or unique, then it's safe to refactor the query this way.
SELECT c.CarId, max(c.CarName) as CarName, min(c.CarColor) as CarColor, COUNT(t.TrailerId) as trailerCount, ...
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId
Not a great improvment but at least you needn't edit GROUP BY every time you need more/less columns from Car.