mysql - 使用 3 个表检索单行

Question

我有 3 个表，即 person、person2、person3。每个表包含两个字段 name 和 phno。如果我给出一个特定的电话号码，查询必须在每个表中显示该号码的存在

我尝试过这样的事情:

select a.name as Name, a.phno,
case when a.phno then 'Y' else 'N' end as Phone_Number1,
case when b.phno then 'Y' else 'N' end as Phone_Number2,
case when c.phno then 'Y' else 'N' end as Phone_Number3
from person as a, person2 as b, person3 as c
where a.phno = '123456' and b.phno = '123456' and c.phno = '123456';

仅当所有表都包含该特定 phno 的值时，此查询才有效。

我需要输出像

phno      Phone_Number1  Phone_Number2  Phone_Number3
123456        Y               Y              Y

如果它出现在所有表格中

phno      Phone_Number1  Phone_Number2  Phone_Number3
123456        N              Y                Y 

如果不存在，则应在该特定表中显示“N”。

Answer 1

我会尝试对每种情况/时间进行比较:

select a.name as Name, a.phno,
case when a.phno = '123456' then 'Y' else 'N'               end as Phone_Number1,
case when b.phno = '123456' then 'Y' else 'N' end as Phone_Number2,
case when c.phno = '123456' then 'Y' else 'N' end as Phone_Number3
from person as a, person2 as b, person3 as c
where a.phno = '123456' or b.phno = '123456' or c.phno = '123456';