CODEWITHSUNDEEP
mysqlsql
TheMethod
TheMethod

Reputation: 3001

Mysql - Summing counts of multiple joined tables

I will do my best to explain this clearly. I am trying to accomplish two things, but am having trouble even getting the first to work correctly.

I have a schema that has a member table which has foreign keys to multiple tables. In the end I am going to be drawing from about 10 tables that may or may not have records for a particular member. I am trying to get the sum of all the counts. My query looks like this:

SELECT (COUNT(tb1.member_id) + COUNT(tb2.member_id)) as total
FROM members m
LEFT JOIN table_1 tb1 ON tb1.member_id = m.member_id
LEFT JOIN table_2 tb2 ON tb2.member_id = m.member_id
WHERE m.member_id = 27

Where 27 is the member_id of the test account I am working with. This doesn't produce accurate results and I believe it is because of the left join, it seems to be throwing things off and I am getting a total of 8 even though there are only two of each kind of record. If I eliminate one of the left joins then I get the expected result.

Could anyone tell me how I should go about doing this?

That is part one of my problem. The second issue is that in some of these cases I will want each result to count as either 1 or 0, that is even if there are 2 or 3 corresponding records. I was looking for something like casting a result as a bool but have not found anything. Could anyone suggest a way to do this?

Thanks much for reading, any advice would be very much appreciated. It could be that I am approaching this problem in the wrong way, again any advice is appreciated.

Upvotes: 2

Views: 452

Answers (1)

Boris Bucha
Boris Bucha

Reputation: 632

Eventhough i am not familiar with state of the art of mysql i am pretty sure something like this will work:

SELECT 
(select COUNT(*) from table_1 = where member_id = m.member_id) 
+ 
(select COUNT(*) from table_2 = where member_id = m.member_id)
as total

FROM members m
WHERE m.member_id = 27

Upvotes: 1

Related Questions