T-SQL multiple COUNT() with JOIN and LEFT JOIN

Question

I have the following query (which does work and returns the expected result) but it's using multiple sub-queries to get the COUNT and I'm concerned that the query will be very inefficient as it is currently written:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        (SELECT COUNT(*) FROM CarImage WHERE CarId = c.Id) AS ImageCount,
        (SELECT COUNT(*) FROM CarLike WHERE CarId = c.Id) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

My initial attempts were without using the sub-queries for the COUNT, which I was able to get working until I added in the LEFT JOIN which then skewed the results of both counts:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        COUNT(ci.CarId) AS ImageCount,
        COUNT(cl.CarId) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

I'm guessing there is a way to get query #2 working and it would be more efficient than query #1?

Answer 1

Just remove the GROUP BY as well as the joins, as they are not necessary

SELECT 
    c.Id                AS Id,
    cd.Make             AS Make,
    u.Id                AS UserId,
    (SELECT COUNT(*) FROM CarImage ci WHERE cl.CarId = c.Id) AS ImageCount,
    (SELECT COUNT(*) FROM CarLike cl WHERE ci.CarId = c.Id) AS LikeCount
FROM Car c
JOIN CarDetail cd   ON c.Id = cd.CarId
JOIN User u         ON c.UserId = u.Id
WHERE c.Status = 'Active';

If the inner-join on CarImage was on purpose then you can use an APPLY and an extra predicate

SELECT 
    c.Id                AS Id,
    cd.Make             AS Make,
    u.Id                AS UserId,
    i.ImageCount,
    (SELECT COUNT(*) FROM CarLike cl WHERE ci.CarId = c.Id) AS LikeCount
FROM Car c
JOIN CarDetail cd   ON c.Id = cd.CarId
JOIN User u         ON c.UserId = u.Id
CROSS APPLY (
    SELECT COUNT(*) AS ImageCount
    FROM CarImage ci
    WHERE cl.CarId = c.Id
) i
WHERE c.Status = 'Active'
  AND i.ImageCount > 0;

You can also put a GROUP BY () into that APPLY and remove the extra predicate to get the same effect as an INNER JOIN, although that can be a very subtle change which other coders may not understand.

CROSS APPLY (
    SELECT COUNT(*) AS ImageCount
    FROM CarImage ci
    WHERE cl.CarId = c.Id
    GROUP BY ()
) i
WHERE c.Status = 'Active';