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
.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…