使用 where 子句从两个表中进行选择查询. [英] Make a select query from two table with where clause.

问题描述

我有两个变量 $code 和 $name.$code 可能有导师代码或学院代码，$name 也可能有导师姓名或学院名称.

I have two variables $code and $name. $code probably have tutor's code or institute's code and$name also probably have tutor's name or institute's name.

这就是 $code 和 $name

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

我的问题是我需要根据这个 $code 和 $name 从 contact 表中获取电子邮件地址.需要检查两个表tutor和institute，它们分别属于code和name.

My problem is I need to get email address from contact table according to this $code and $name. Need to check two table tutor and institute which belong to code and name.

tutor 和 institute 表有 contact_id 和 contact 表也有 contact_id.tutor 表有tutor_code 和tutor_name.
institute 表有 institute_code 和 institute_name

tutor and institute table have contact_id and contact table also have contact_id. tutor table have tutor_code and tutor_name.
institute table have institute_code and institute_name

我尝试过这样的事情.但不能同时签入两个表.

I tried something like this. but can't check in both tables.

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

希望有人能帮助我.谢谢.

Hope someone will help me. Thank you.

推荐答案

你可以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.

keep it mind that it will return duplicate record if both records exists on both table because of specifying ALL in the UNION. If you want to get only unique records, remove ALL.

这篇关于使用 where 子句从两个表中进行选择查询.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！