MySQL查询缺失数据

Question

我试图从1个数据库中的3个MySQL表中查询数据，这些表由固定街道名称(Lucas st.)、电话类型(home/office/mobile)和地址类型(home/office)过滤。还必须包括缺少数据的行(例如。有人没有手机，还得查询一下)。由于在MySQL中不支持完全连接，所以我尝试了JOIN和UNION，但是到目前为止没有结果。

SELECT first_name, last_name, street, building, apartment, phone_number  
FROM person  
JOIN phone ON person.id = phone.person_id  
JOIN address ON phone.person_id = address.person_id WHERE address.street='Lucas st.' AND phone.type='home' AND address.type='home';

如有任何建议和想法，将不胜感激。提前谢谢你。

Answer 1

SELECT first_name, last_name, street, building, apartment, phone_number  
FROM person  
LEFT JOIN phone   ON person.id = phone.person_id  
                 AND phone.type='home'
LEFT JOIN address ON phone.person_id = address.person_id 
                 AND address.street='Lucas st.'  
                 AND address.type='home';