CODEWITHSUNDEEP
mysqlsql
shashank
shashank

Reputation: 25

Converting nested select into join

My query is 

select COUNT(*) from result 
where test_id in (select test_id 
                    from test_schedule 
                    where scheduler_id in (select user_id 
                                           from users 
                                           where user_type=1))

Upvotes: 1

Views: 81

Answers (3)

Barmar
Barmar

Reputation: 780843

SELECT COUNT(*)
FROM result r
JOIN (SELECT DISTINCT test_id
      FROM test_schedule s
      JOIN users u ON s.scheduler_id = u.user_id
      WHERE u.user_type = 1) s
USING (test_id)

The DISTINCT is necessary to keep rows from being multiplied by all the rows in the other tables that match.

Upvotes: 0

bikedorkseattle
bikedorkseattle

Reputation: 981

SELECT COUNT(r.*)
FROM result r
RIGHT JOIN test_schedule s USING(test_id)
RIGHT JOIN users u ON s.scheduler_id  = u.user_id
WHERE u.user_type = 1

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Try this:

SELECT COUNT(r.*)
FROM result r
INNER JOIN test_schedule s ON r.test_id = s.test_id
INNER JOIN users u ON s.scheduler_id  = u.user_id
WHERE u.user_type = 1

Upvotes: 1

Related Questions