MySql Join Query left join

Question

I have table name students having fields student_id, house_id etc.. and subjects, houses, students_subjects

I am using this query

SELECT students_subjects.student_id,students.house_id,students_subjects.subject_id,subjects.subject_name,students.rollno,students.first_name, students.last_name FROM 
students_subjects LEFT JOIN students on students_subjects.student_id=students.id
LEFT JOIN subjects on students_subjects.subject_id=subjects.id  WHERE students_subjects.class_years_section_id=1

This is working fine for me ..

Now i want to get house name too from houses table

I tried this query

SELECT students_subjects.student_id,students.house_id,houses.house_name, students_subjects.subject_id,subjects.subject_name,students.rollno,students.first_name, students.last_name FROM 
students_subjects LEFT JOIN students on students_subjects.student_id=students.id
LEFT JOIN subjects on students_subjects.subject_id=subjects.id

LEFT JOIN houses on students.house_id=houses.id WHERE students_subjects.class_years_section_id=1

AND students_subjects.school_session_id=1 AND students.is_active=1

and it gives me house_name = NULL

Can anybody tell me how to get house name too . using join query

Thanks

This is common MySQL knowledge. You cannot put a JOIN after a WHERE statement. — Thomas Potaire
– Thomas Potaire, Commented Mar 18, 2013 at 5:10

John Woo · Accepted Answer · 2013-03-18 05:43:38Z

7

The error in your query is caused by the LEFT JOIN keyword that is after WHERE clause,

SELECT  students_subjects.student_id,
        students.house_id,
        students_subjects.subject_id,
        subjects.subject_name,
        students.rollno,
        students.first_name, 
        students.last_name 
FROM    students_subjects 
        LEFT JOIN students 
            on students_subjects.student_id=students.id
        LEFT JOIN subjects 
            on students_subjects.subject_id=subjects.id
        LEFT JOIN houses 
            on students.house_id=houses.id
WHERE   students_subjects.class_years_section_id = 1 AND 
        students_subjects.school_session_id = 1 AND 
        students.is_active = 1

Remember that JOINs are part of the FROM Clause.

UPDATE 1

SELECT  b.student_id,
        a.house_id,
        b.subject_id,
        c.subject_name,
        a.rollno,
        a.first_name, 
        a.last_name,
        d.house_name            
FROM    students a
        INNER JOIN students_subjects b
            ON b.student_id = a.id
        INNER JOIN subjects  c
            ON b.subject_id = c.id
        INNER JOIN houses d
            ON a.house_id = d.id
WHERE   b.class_years_section_id = 1 AND 
        b.school_session_id = 1 AND 
        a.is_active = 1

edited Mar 18, 2013 at 5:43

answered Mar 18, 2013 at 5:10

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Woo Over a year ago

what do you want to know more?

Rashmi Over a year ago

i am trying to fetch house name from houses table too . but it shows me null

John Woo Over a year ago

first, can you explain to me what do you want to do? I see you are joining students_subjects from all tables. If there is no student then the value of house is obviously null.

Rashmi Over a year ago

i have table students where i have fields house_id and another table named houses having field id, house_name, and students_subjects table having field student_id.

Rashmi Over a year ago

Now i need to get house name too

|

Dion D. · Accepted Answer · 2013-03-18 05:14:16Z

0

You've miss placed the WHERE clause, try this:

SELECT students_subjects.student_id,students.house_id,students_subjects.subject_id,subjects.subject_name,students.rollno,students.first_name, students.last_name 
FROM students_subjects 
     LEFT JOIN students ON students_subjects.student_id=students.id
     LEFT JOIN subjects ON students_subjects.subject_id=subjects.id  
     LEFT JOIN houses ON students.house_id=houses.id
WHERE students_subjects.class_years_section_id=1 
AND students_subjects.school_session_id=1 AND students.is_active=1

answered Mar 18, 2013 at 5:14

Dion D.

2,5962 gold badges18 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySql Join Query left join

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related