mysql count records from two tables in one query?

count, mysql

Solution

I think this should work assuming your tsId and paId are unique keys:

SELECT Count(DISTINCT t.tsID) AS tsCount, 
    Count(DISTINCT p.paID) AS paCount
FROM account a 
    LEFT JOIN test t ON a.acId = t.tsAccountId
    LEFT JOIN patient p ON a.acId = p.paAccountId
WHERE a.acId = 1

And here is the SQL Fiddle.

Please note: the problem with not joining on the account table (and using it as the master table) is that if either the test table or the patient table have no data for a specific account id, the query will return 0 results for each -- which could be incorrect.

Problem

I have three MySQL-tables: ``` patient: paID, paCode, paAccountID (foreign key) test tsID, tsName, tsPatientID (foreign key), tsAccountID (foreign key) account acID etc. ``` Now I want to count the number of `paID` and the number of `tsID` that are linked to a specific `acID=1`. There are 6 `tsID` and 4 `paID` linked to `acID=1`. ``` SELECT Count(paID) AS paCount FROM patient WHERE paAccountID=1 SELECT Count(tsID) AS tsCount FROM test WHERE tsAccountID=1 ``` Tried to get both into one query... ``` SELECT Count(tsID) AS tsCount, Count(paID) AS paCount FROM test LEFT JOIN patient ON tsPatientID = paID WHERE tsAccountID=1 ``` Doesn't work that way, both Counts return 6. How to get it right?

Original source