2

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.

2 Answers 2

4

You can get this by changing each IN subquery into a join, and matching the columns in the join condition.

SELECT Q.*
FROM Questions Q
INNER JOIN Subjects S ON Q.SubjectId = S.SubjectId
INNER JOIN Schools_Subjects SS ON S.ParentSubjectId = SS.SubjectId
WHERE SS.SchoolId = 5 -- or IN (a, b, c)

Then you can limit the columns returned to just those from Questions by restricting the result set to just those columns.

However, as you want to be able to run this against multiple schools at a time, then JOIN is probably not the best -- unless you're willing to slap a DISTINCT on the first line.

You can avoid the DISTINCT by switching to using the EXISTS construct like so...

SELECT Q.*
FROM Questions Q
WHERE EXISTS(
    SELECT *
    FROM Subjects S
    INNER JOIN Schools_Subjects SS ON S.ParentSubjectId = SS.SubjectId
    WHERE Q.SubjectId = S.SubjectId
    AND SS.SchoolId = 5 /* or IN (a, b, c) */
)
2
  • Can you please elaborate your last sentence i.e. using of EXISTS instead of DISTINCT.
    – jin jan
    Apr 27, 2022 at 15:04
  • @jinjan so the problem with DISTINCT is that it'll apply at the very end of the processing, so you might have many, many duplicate rows being returned through the query and taking up processing and memory before being filtered out at the last moment. EXISTS allows the query to generally choose a more efficient route. For small queries like this it's not likely to be noticeable but it's better IMO to get into good habits.
    – TZHX
    Apr 29, 2022 at 7:06
3

I think this is what you are looking for:

select q.*
from Questions q
inner join Subjects s on q.SubjectId=s.SubjectId
inner join Schools_Subjects ss on s.ParentSubjectId=ss.SubjectId
where ss.SchoolId = 5;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5e465d4f3fb540ef0d3cdb99715803aa

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.