mysql select, how to

1 Views

I need help.
I have 2 tables with names bkeys ( id, bkey, ip, user_id ) and wp_usermeta (yes, wordpress table, fields of interest are user_id, meta_key and meta_value).
What I need is to select all rows from bkeys + value of meta_value where key is nickname, so result will have fields bkey, ip and meta_value.
The problem is, bkeys table can have NULL for ip and user_id (meaning keys is not taken by any user yet) so rows with NULL for user_id won’t be selected with query like 

SELECT bkeys.bkey, bkeys.ip, wp_usermeta.meta_value
FROM bkeys
LEFT JOIN wp_usermeta ON ( bkeys.user_id = wp_usermeta.user_id )
WHERE meta_key='nickname'

what i need is that all rows are selected, even where user_id is null. Is it possible? If it is, how?

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. Try moving the meta_key = 'nickname' to the ON clause:

    SELECT bkeys.bkey, bkeys.ip, wp_usermeta.meta_value
FROM bkeys
LEFT JOIN wp_usermeta ON wp_usermeta.user_id = bkeys.user_id
                     AND wp_usermeta.meta_key = 'nickname'

    Think about LEFT JOINs as a two-phase filtering:

    • First you try to join a row with the ON condition, else you join a NULLed row
    • You can further filter the joined or NULLed rows at the WHERE condition.