MySql的连接查询左连接

问题描述：

我已经表名学生其领域student_id数据，house_id等。和主题，房屋，students_subjectsMySql的连接查询左连接

我使用此查询

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

这是工作对我罚款..

现在我想从屋表弄房子的名字太

我尝试这个查询

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上students.house_id = houses.id 房子里students_subjects.class_years_section_id = 1

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

，这让我house_name = NULL

任何人都可以告诉我如何获得房子名称。使用连接查询

感谢

这是常见的[MySQL的知识（HTTP：//开发。 mysql.com/doc/refman/5.0/en/select.html）。你不能在'WHERE'语句之后加入'JOIN'。 – 2013-03-18 05:10:51

答

查询中的错误是由LEFT JOIN关键词是后WHERE条款引起的，

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

记住JOIN s为的FROM条款的一部分。

更新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

你想知道更多什么？ – 2013-03-18 05:35:01

我正在尝试从房子表中获取房子名称。但它首先显示我 – Rashmi 2013-03-18 05:35:07

，你能向我解释你想做什么？我看到你正在加入所有表中的'students_subjects'。如果没有学生，那么房子的价值显然为零。 – 2013-03-18 05:36:56

答

你已经错过放在WHERE条款，试试这个：

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

MySql的连接查询左连接

相关推荐