使用where子句从两个表中执行select查询 - Make a select query from two table with where clause.

Make a select query from two table with where clause.

我有两个变量$code和$name。$code probably have tutor's code or institute's code and $name也可能有导师的名字或研究所的名字。

这是$code和$name名称的由来

// Check Tutor or Institute
if ( $tutorCode && $tutorName) {
    $code = $tutorCode;
    $name = $tutorName;
} elseif ( $instituteCode && $instituteName) {
    $code = $instituteCode;
    $name = $instituteName;
}

我的问题是我需要根据这个$code和$name从contact表中获取电子邮件地址。需要查看code和name两个表tutor和institute。

tutor和institute表有contact_id, contact表也有contact_id。tutor表中有tutor_code和tutor_name。
institute表有institute_code和institute_name

我试过这样做。但是不能同时签入两个表

$q = "SELECT email FROM tutor 
      WHERE tutor_code = $code AND tutor_name = $name"

希望有人能帮助我。谢谢你。

你可以UNION这两个表

<罢工>

SELECT  email, code, name
FROM
(
    SELECT email, tutor_code as code, tutor_name as Name FROM tutor
    UNION ALL
    SELECT email, institute_code  as code, institute_name as Name FROM institute
) sub
WHERE   code = $code AND
        name = '$name'

或

SELECT  s.*, c.*
FROM
        (
            SELECT  contact_ID, 
                    tutor_code as code, 
                    tutor_name as Name,
                    'TUTOR' sourceTbl
            FROM    tutor
            WHERE   tutor_code = $code AND
                    tutor_name = '$name'
            UNION ALL
            SELECT  contact_ID,
                    institute_code  as code, 
                    institute_name as Name,
                    'INSTITUTE' sourceTbl
            FROM    institute
            WHERE   institute_code = $code AND
                    institute_name = '$name'
        ) s
        INNER JOIN contact c
            ON s.contact_ID = c.contact_ID

请记住，如果两个表中都存在记录，则会返回重复记录，因为在UNION中指定了ALL。如果您只想获得唯一的记录，请删除ALL。

你应该好好学习一下在mysql中使用join。

http://dev.mysql.com/doc/refman/5.0/en/join.html

对于institute和tutor部分，您都可以使用UNION或join。在你的例子中，它看起来像这样:

$q = "SELECT c.email 
      FROM contact c 
      LEFT JOIN tutor t on t.contact_id = c.contact_id
      LEFT JOIN institute i on i.contact_id = c.contact_id
      WHERE 
      ( 
          (t.tutor_code = $code AND t.tutor_name = $name) OR
          (i.institute_code = $code AND i.institute_name = $name) OR
      )";

查看您的查询，我注意到$code和$name值存在问题。您需要在双引号或单引号内添加$code和$name