Clever Geek Handbook

Mysql counts records from two tables in one query?

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 quantity paID

and number tsID

that are associated with a specific one acID=1

. There are 6 tsID

and 4 paID

related 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 in one request ...

SELECT Count(tsID) AS tsCount, Count(paID) AS paCount
FROM test LEFT JOIN patient ON tsPatientID = paID 
WHERE tsAccountID=1

Doesn't work like this, both Counts are returned 6. How to do this correctly?

+3


source to share


3 answers


I think this should work if 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 the accounts table (and using it as the master table) is that if there is no data in the test table or patient table for a specific account id, the query will return 0 results for each - which could be wrong.

+4


source 


SELECT
    COUNT(DISTINCT tsID) AS tsCount,
    COUNT(DISTINCT paID) AS paCount
FROM
    test
    CROSS JOIN patient
WHERE
    tsAccountID = 1
    AND paAccountID = 1


0


source


your join method returns all associated rows, so your count will always return the number of all rows returned

in such cases I would use (select count (*) from [Table] Where [id = x]) as [tsCount]) inside the main select query as a field .. that

-1


source






More articles:


All Articles