SQL语句4表连接

Question

您好，目前我的数据库中有4个表: tb_student，tb_history，tb_section和tb_adviser。到目前为止，我使用这一行向我展示了一个给定学生的历史：

   $qry_display = "SELECT
        a.student_id, a.section_id, a.level, a.photo, a.address, a.father_occupation, a.father_phone, a.father_company, a.mother_occupation, a.mother_phone, a.mother_company,a.gpa,
        b.fname, b.sex, b.lname, b.mname, b.birth_date, b.birth_place, b.address, b.father, b.father_degree, b.mother, b.mother_degree,
        c.section_name, c.adviser_id
        FROM tbl_er AS a
        LEFT OUTER JOIN tbl_enroll AS b ON a.student_id = b.student_id
        LEFT OUTER JOIN tbl_section AS c ON a.section_id = c.section_id
        WHERE a.student_id=".$id." AND a.level='1st Year'";

我的主要问题是现在我需要显示顾问的姓氏和这些其他信息。所以我在考虑把。请注意，tb_adviser仅通过adviser_id连接到tb_section

     LEFT OUTER JOIN tbl_adviser AS d ON a.student_id = c.adviser_id

我在where语句之前添加了这一行。并将此行插入到我的选择字段中。

     d.lname_adviser

目前它不起作用。任何人都会对我的问题有所了解。

Answer 1

查看db结构会有所帮助，但看起来您正在尝试在两个不匹配的列上进行连接。a.student_id会与c.adviser_id相匹敌吗？

假设tbl_advisor中的advisor_id是id，那么只需添加

 LEFT OUTER JOIN tbl_advisor AS d 
 ON d.id = c.advisor_id

Answer 2

试试这个：

我觉得你应该这样做

LEFT OUTER JOIN tbl_adviser AS d ON d.adviser_id = c.adviser_id 

因此，您的查询将是：

Select .....
FROM tbl_er AS a
LEFT OUTER JOIN tbl_enroll AS b ON a.student_id = b.student_id
LEFT OUTER JOIN tbl_section AS c ON a.section_id = c.section_id
LEFT OUTER JOIN tbl_adviser AS d ON d.adviser_id = c.adviser_id 
WHERE a.student_id=".$id." AND a.level='1st Year'