I'm attempting to write a query that returns the count of a given value across multiple tables.
Right now, I'm using the following query
SELECT COUNT(*) AS SL, ( SELECT COUNT(*) FROM [Sales Line Archive] WHERE [Document No_] = 123 ) AS SLA FROM [Sales Line] WHERE [Document No_] = 123
Which returns a single result with the count of lines in Sales Line in the first column and the count from Sales Line Archive in the second.
However, I'd like to request multiple records using an
IN clause, so that I could get results like the following
Document No_, SL, SLA 1, 3, 0 2, 0, 4 3, 2, 1