Amit Bravo Amit Bravo - 5 months ago 14
SQL Question

how to JOIN table optional?

Here is my code : The only problem is, it returns results only when atleast one list available in wp_realcustomers table, but I want it return results whether or not it has a list/data in wp_realcustomers, making it somekind of optional.

"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

Use LEFT JOIN on 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 )
LEFT JOIN wp_realcustomers customers ON(usermeta1.user_id = customers.team_member_id )


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

If You have count you must have 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 "
Comments