mysql - 如何从12个表格中获取不匹配的记录

我正在使用下面的代码，但没有得到任何结果，任何人都可以给我建议。

users_register表具有所有用户的手机号码，我有12个表，例如property，hostels，hotels等。

如何获取不匹配的手机号码

SELECT t1.user_mobileno,
       t1.user_fullname
FROM users_register t1
LEFT JOIN userpostproperties t2 ON t1.user_mobileno = t2.property_contact_number
LEFT JOIN services t3 ON t1.user_mobileno=t3.contact_num
LEFT JOIN hostels t4 ON t1.user_mobileno=t4.h_contact_num
LEFT JOIN sales t5 ON t1.user_mobileno=t5.sales_contact
LEFT JOIN rentals t6 ON t1.user_mobileno=t6.rental_mobile
LEFT JOIN jobs t7 ON t1.user_mobileno=t7.emp_mobile
LEFT JOIN healthcare t8 ON t1.user_mobileno=t8.contact_num
LEFT JOIN education t9 ON t1.user_mobileno=t9.edu_mobile
LEFT JOIN hotels t10 ON t1.user_mobileno=t10.contact_num
LEFT JOIN restaurant t11 ON t1.user_mobileno=t11.contact_num
LEFT JOIN finance t12 ON t1.user_mobileno=t12.contact_no
LEFT JOIN manufacturer t15 ON t1.user_mobileno=t15.contact_no

最佳答案

要从users_register表中获取不匹配的记录，可以在LEFT JOIN子句中将IS NULL与列名WHERE一起使用。

这是您所期望的吗

SELECT t1.user_mobileno,
       t1.user_fullname
FROM users_register t1
LEFT JOIN userpostproperties t2 ON t1.user_mobileno = t2.property_contact_number
LEFT JOIN `services` t3 ON t1.user_mobileno = t3.contact_num
LEFT JOIN hostels t4 ON t1.user_mobileno = t4.h_contact_num
LEFT JOIN sales t5 ON t1.user_mobileno = t5.sales_contact
LEFT JOIN rentals t6 ON t1.user_mobileno = t6.rental_mobile
LEFT JOIN jobs t7 ON t1.user_mobileno = t7.emp_mobile
LEFT JOIN healthcare t8 ON t1.user_mobileno = t8.contact_num
LEFT JOIN education t9 ON t1.user_mobileno = t9.edu_mobile
LEFT JOIN hotels t10 ON t1.user_mobileno = t10.contact_num
LEFT JOIN restaurant t11 ON t1.user_mobileno = t11.contact_num
LEFT JOIN finance t12 ON t1.user_mobileno = t12.contact_no
LEFT JOIN manufacturer t15 ON t1.user_mobileno = t15.contact_no
WHERE   t2.property_contact_number IS NULL AND
        t3.contact_num IS NULL AND
        t4.contact_num IS NULL AND
        t5.sales_contact IS NULL AND
        t6.rental_mobile IS NULL AND
        t7.emp_mobile IS NULL AND
        t8.contact_num IS NULL AND
        t9.edu_mobile IS NULL AND
        t10.contact_num IS NULL AND
        t11.contact_num IS NULL AND
        t12.contact_no IS NULL AND
        t15.contact_no IS NULL