CODEWITHSUNDEEP
sqlsql-server
user17503319
user17503319

Reputation: 21

how can I convert this SQL statement with nested IN clauses to a JOIN format

Here are the very simplified versions of my three tables.

Schools_Subjects Table

SchoolId SubjectId
1 2
5 1
5 6

The above table contains only parent subject IDs.

Subjects Table

SubjectId Name ParentSubjectId
1 Science {NULL}
2 Mathematics {NULL}
3 Biology 1
4 Physics 1
5 Chemistry 1
6 History {NULL}
7 Elementary Math 2
8 Calculus 2

Questions Table

QuestionId Text SubjectId
1 What is 2 + 2 7
2 Tell me the fastest animal name 3
3 Salt is composed of which two elements 5
4 How to divide 6 apples among 3 students 7

I want to fetch all the questions given a (or multiple) school ID. For example for schoolId:5, I have the below SQL query:

SELECT * 
FROM   Questions 
WHERE  SubjectId IN (
           SELECT SubjectId 
           FROM   Subjects 
           WHERE  ParentSubjectId IN (
               SELECT SubjectId 
               FROM   Schools_Subjects 
               WHERE SchoolId = 5
           )
       )

My above query works but I want to change it into a JOIN format query.

I work on SQL Server, but a ANSI-SQL query will be highly appreciated.

Upvotes: 0

Views: 55

Answers (1)

Onyambu
Onyambu

Reputation: 79288

If using Mysql:

SELECT Q.* 
FROM   Questions Q
JOIN (
      SELECT S.SubjectId 
      FROM  Subjects S 
      JOIN  Schools_Subjects SS
      ON  S.ParentSubjectId = SS.SubjectId AND SS.SchoolId = 5
      ) t1
ON Q.SubjectID = t1.SubjectId

 QuestionId                                   Text SubjectId  X
1          2        Tell me the fastest animal name         3 NA
2          3 Salt is composed of which two elements         5 NA

Which is the same results produced by your code

Upvotes: 1

Related Questions