CODEWITHSUNDEEP
mysql
guessguess hoo
guessguess hoo

Reputation: 45

join three tables and check record

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

Upvotes: 0

Views: 67

Answers (1)

MandyShaw
MandyShaw

Reputation: 1156

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

Upvotes: 3

Related Questions