join three tables and check record

Question

I want to join three tables and check the two other tables if the user has record on it.

Tables:

table1
id | username | is_active

table2
id | userid | amount

table3
id | userid | amount

I want to get and COUNT the user where 'is_active' = 1 and no records on table 2 and table 3

I'm trying this:

SELECT c.id,c.is_active, 
       COUNT(c.id) AS count,
       COUNT(a.userid) AS count1,
       COUNT(b.userid) AS count2
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`userid` 
       LEFT JOIN `table2` a 
              ON b.`userid` = a.`userid` 
WHERE  c.`is_active` = 1 
GROUP BY c.id

You use `count` aggregate for multiple columns, so you need `GROUP BY` for them - possible duplicate of https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns — dmitryro, Jul 19 '18 at 21:32
If not, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jul 19 '18 at 21:35
what i want is, get all the users where is_active = 1 and no records on table 2 and table 3 — guessguess hoo, Jul 19 '18 at 21:42

MandyShaw · Accepted Answer · 2018-07-21T06:43:57.527

How about this?

select count(*) as count from
(SELECT distinct c.id
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`userid` 
       LEFT JOIN `table2` a 
              ON c.id = a.`userid` 
WHERE  c.`is_active` = 1 and a.userid is null and b.userid is null) s1

This counts the unique c.id's which have is_active set and which have no corresponding a.userid or b.userid.

You could omit the 'distinct' in fact, assuming that c.id is the primary (and therefore unique) key of tbl_members. That would make it simpler:

select count(*) 
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`userid` 
       LEFT JOIN `table2` a 
              ON c.id = a.`userid` 
WHERE  c.`is_active` = 1 and a.userid is null and b.userid is null

@guessguesshoo I've just corrected the second left join in each of the above, the original version would have let in rows with a b but no a, I think. — MandyShaw, Jul 21 '18 at 06:45

join three tables and check record

1 Answers1