Fetch data from different tables on same row

I came across an interesting problem this week. The requirement was to get row counts from two different tables and return these on the same row. The tables had a common column, lets call it category.

The first thought was to use GROUP BY in T-SQL, but there is a problem with this:

SELECT a.Category, COUNT(a.*), b.Category, COUNT(b.*)
FROM TableA a INNER JOIN TableB b ON b.Category = a.Category

The problem with this code is that it gives the cartesian product of A and B, but I would like to know the count of A and the count of B on the same row.

There may well be better ways of doing it, but the way I solved it this time was to use CTE’s (common table expressions). The example T-SQL code using CTE’s would be as follows:

; WITH cte1 (cat, cnt)
AS
(
SELECT a.Category, COUNT(*)
FROM TableA a
), cte2 (cat, cnt)
AS
(
SELECT b.Category, COUNT(*)
FROM TableB b
)
SELECT cte1.cat, cte1.cnt AS CountA, cte2.cnt AS CountB
FROM cte1 INNER JOIN cte2 ON cte2.cat = cte1.cat

My live example was a little more complicated than this but I hope this demonstrates the concept.