php - Make a select query from two table with where clause.

Question

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.

This is the way the names come to $code and $name

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

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 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.

Answer 1

you can UNION both tables

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'

or

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

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.