How to use inner join and left join with more than two tables-CodePudding

I am using here three tables first one is a student table and the second one is a Branch table and the third one is an Address table

select [Name],Br_name,City 
from student
inner join Branch
on student.Br_id=Branch.Br_id
left join [Address]
on student.City_id=[Address].City_id

I have three tables I want to show here the student's name and branch name city name, but I want to show the student who has their branch only. I also want to show the student who has their city as well as show a student who does not have any city.

I wrote query above but here I am not getting the result. Here student's name who does not have any city what's wrong here in my above join SQL query?

Why I am not able to get the students who do not have any city as well as who have a city? What's wrong here in my join query what I am missing here? How can I write a query for getting the result? How can I write a query so that I will get the result that I am expecting?

CodePudding user response：

You actually need to step back a bit. Break down your thoughts as it sounds like you have TWO queries in question. Start with the one where NO city/branch is found. For this, you need a LEFT-JOIN, meaning, I want all things from THIS-LEFT side (Students) where there IS NOT NECESSARILY a match in the right-side (Branch).

Also, start to use table "alias" references to simplify readability, especially when you get to using the same table multiple times in the same query. And always qualify columns such as table.column or alias.column so others know where data is coming from and dont have to guess at your table structures.

select
      s.Name
   from
      student s
         LEFT JOIN branch b
            on s.br_id = b.br_id
   where
      b.br_id IS NULL

Notice above, the LEFT-JOIN is to the branch, but the WHERE clause is explicitly looking for students to DO NOT HAVE a match in the branch table, thus the b.br_id is NULL

Back to your FIRST issue. Getting all students within the same branch/city? That would be an INNER-JOIN meaning you want the records that DO match on each part of the join. But what is the basis. If you have Student "X" who lives in branch/city "Y" that you want all students who live in the same branch "Y"? What is the criteria for which student you are trying to restrict the branch down to. I can only guess there is a student id of interest. From that, I would do the following.

Prequery the student you want and the branch/city that person resides. THEN Join to the student table again for all others in the same branch. Perfect example of same table used multiple times in same query (also using aliases to clarify)

select
      s1.Name PrimaryStudent,
      b.br_name,
      b.city,
      s2.Name OtherStudentInSameBranch
   from
      student s1
         JOIN branch b
            on s1.br_id = b.br_id
         JOIN student s2
            on s1.br_id = s2.br_id
   where
      s1.studentId = 1234

SO here, the primary table of S1 student I am looking for a single person via the where clause. From that, I am joining AGAIN to the same student table with alias S2 but based on the branch being the same as the first student. Since all are pointing to the same branch, I only need one join to the branch table to pull the branch name and city.