mysql - 如何JOIN表可选？

Question

唯一的问题是，我的代码仅在 wp_realcustomers 表中至少有一个列表可用时才返回结果，但我希望它返回结果，无论它在 中是否有列表/数据>wp_realcustomers，使其成为某种可选的。

这是我的代码:

"SELECT 
    usermeta1.user_id,
    usermeta2.meta_value AS firstname,
    usermeta3.meta_value AS lastname,
    usermeta4.meta_value AS phone1,
    usermeta5.meta_value AS phone2,
    mainuser.user_email AS email,
    COUNT(customers.id) AS numberofcustomers


FROM wp_usermeta usermeta1
JOIN wp_usermeta usermeta2 ON(usermeta1.user_id = usermeta2.user_id AND usermeta2.meta_key = 'first_name' )
JOIN wp_usermeta usermeta3 ON(usermeta1.user_id = usermeta3.user_id AND usermeta3.meta_key = 'last_name' )
JOIN wp_usermeta usermeta4 ON(usermeta1.user_id = usermeta4.user_id AND usermeta4.meta_key = 'tm_phone_1' )

JOIN wp_usermeta usermeta5 ON(usermeta1.user_id = usermeta5.user_id AND usermeta5.meta_key = 'tm_phone_2' )
JOIN wp_users mainuser ON(usermeta1.user_id = mainuser.ID )
JOIN wp_realcustomers customers ON(usermeta1.user_id = customers.team_member_id )


WHERE usermeta1.meta_key='teamleader_id' && usermeta1.meta_value='1'

Answer 1

在 wp_realcustomers 上使用 LEFT JOIN

"SELECT 
    usermeta1.user_id,
    usermeta2.meta_value AS firstname,
    usermeta3.meta_value AS lastname,
    usermeta4.meta_value AS phone1,
    usermeta5.meta_value AS phone2,
    mainuser.user_email AS email,
    COUNT(customers.id) AS numberofcustomers


FROM wp_usermeta usermeta1
JOIN wp_usermeta usermeta2 ON(usermeta1.user_id = usermeta2.user_id AND usermeta2.meta_key = 'first_name' )
JOIN wp_usermeta usermeta3 ON(usermeta1.user_id = usermeta3.user_id AND usermeta3.meta_key = 'last_name' )
JOIN wp_usermeta usermeta4 ON(usermeta1.user_id = usermeta4.user_id AND usermeta4.meta_key = 'tm_phone_1' )

JOIN wp_usermeta usermeta5 ON(usermeta1.user_id = usermeta5.user_id AND usermeta5.meta_key = 'tm_phone_2' )
JOIN wp_users mainuser ON(usermeta1.user_id = mainuser.ID )
LEFT JOIN wp_realcustomers customers ON(usermeta1.user_id = customers.team_member_id )


WHERE usermeta1.meta_key='teamleader_id' && usermeta1.meta_value='1'" 

如果你有 count 你必须有 group by

"SELECT 
    usermeta1.user_id,
    usermeta2.meta_value AS firstname,
    usermeta3.meta_value AS lastname,
    usermeta4.meta_value AS phone1,
    usermeta5.meta_value AS phone2,
    mainuser.user_email AS email,
    COUNT(customers.id) AS numberofcustomers


FROM wp_usermeta usermeta1
JOIN wp_usermeta usermeta2 ON(usermeta1.user_id = usermeta2.user_id AND usermeta2.meta_key = 'first_name' )
JOIN wp_usermeta usermeta3 ON(usermeta1.user_id = usermeta3.user_id AND usermeta3.meta_key = 'last_name' )
JOIN wp_usermeta usermeta4 ON(usermeta1.user_id = usermeta4.user_id AND usermeta4.meta_key = 'tm_phone_1' )

JOIN wp_usermeta usermeta5 ON(usermeta1.user_id = usermeta5.user_id AND usermeta5.meta_key = 'tm_phone_2' )
JOIN wp_users mainuser ON(usermeta1.user_id = mainuser.ID )
LEFT JOIN wp_realcustomers customers ON(usermeta1.user_id = customers.team_member_id )


WHERE usermeta1.meta_key='teamleader_id' && usermeta1.meta_value='1'
group by  usermeta1.user_id, usermeta2.meta_value , 
          usermeta3.meta_value , usermeta4.meta_value ,
          usermeta5.meta_value , mainuser.user_email "