Left join multiple tables onto one table [closed]

Question

I want to left join multiple tables to one table. The tables are themselves results of subqueries.

A classical example that comes to my mind is, I have a bunch of subqueries:
1. Subquery A gives me details of students - say table 1
2. Subquery B gives me student scores in Math - say table 2
3. Subquery C gives me student scores in English - say table 3
The tables contain scores only if the student has taken that test and the student is to be considered failed if he/she has not taken test at all (or has a score < passing score). I have student IDs (unique per person) in each table to join on.

What do I want from these? I am trying to build a dynamic query (where some parts are populated at runtime by an external mechanism) by performing some joins on these tables to give me:
1. Students who passed in both tests and corresponding scores
2. Students passed in either test, but failed (or did not take) the other test and the corresponding scores (NULL if not taken). 3. All students and their corresponding scores.

What I have on mind is left joining each score table to student profile table. How should I go about this?

Before you go ahead and suggest table 1 left join table 2 left join table 3, this structure will cause problems if, say table 2 contains a null record for a particular student (as per my knowledge). And this basically joins table 3 on table 2 and not on table 1, from my understanding, which is what I want.

PS: Feel free to suggest better ways to get what I need, if you know any.

Answer 1

You can create the appropriate relations by writing carefully your query:

select 
    t1.*, t2.foo, t3.bar
from
    table1 as t1
    left join table2 as t2 on t1.id = t2.id
    left join table3 as t3 on t1.id = t3.id

As you can see, table3 is related to table1 (not to table2), which is what you want.