sqlmysqlmariadb

How to get the number of rows from multiple related tables with one query

I have four tables, one primary and three dependents, let's call them A, B, C and D.
All tables have the column ID, with ID being the primary key for A and the foreign key for B-D.
B-D also have a column called timestamp and (non-unique) indices combining ID and timestamp (in this order).

For a given subset of ID's from 'A', how do I get the number of rows in the tables B-D, optionally filtered by a range of timestamps?
Preferably without using dependent subqueries, because these are (supposedly) 'very inefficient'.

What I've tried is something like this:

SELECT A.`ID`,
       (COUNT(`B`.`timestamp`) + COUNT(`C`.`timestamp`) + COUNT(`D`.`timestamp`)) as `eventCount`
FROM `A`
LEFT JOIN `B` ON A.`ID` = `B`.`ID`
LEFT JOIN `C` ON A.`ID` = `C`.`ID`
LEFT JOIN `D` ON A.`ID` = `C`.`ID`
GROUP BY A.`ID`

However this yields a ridiculously high number akin to a cartesian product (10296 instead of 26 + 12 + 11, as the actual data for a specific row would be).
I have considered using COUNT(DISTINCT...), but am worried that it would also filter out duplicate timestamps, which are perfectly possible.

I have added a fiddle for you to try your queries against. Additionally I want to note, that I do not, in fact, have 'a small set of data', I am talking about multiple 10ks of rows, which is why I am worried about efficiency to begin with.

However the query itself should have little to do with 'how many rows' there are.

Solution

  • If you can have duplicated values in the columns that you want to count, then you have to do the count table by table, independently from each other.

    If you join the tables, then you can count the actual rows or unique values only.

    Considering you want to count rows in a tables corresponding to a criteria, a joining them into a single large dataset will make the operation more costly than doing independent queries for A+B, ..., A+D style as the combined resultset is bigger.

    You can use a union or derived tables to get the counts in a single overarching query, but the counting must be done table by table.

    Sample query with union:

    SELECT 'B' as table_name, `A`.ID as a_id, count(B.timestamp) as cnt FROM`A` LEFT JOIN `B` ON A.`ID` = `B`.`ID` GROUP BY `A`.ID
UNION ALL
SELECT 'C', `A`.ID, count(C.timestamp) FROM`A` LEFT JOIN `C` ON A.`ID` = `C`.`ID` GROUP BY `A`.ID
...