SQL: joining 3 tables, needing to also join the 3rd to itself

Question

This works if I remove the final JOIN line, but I'm not sure why it won't work with it. The last two JOIN statements are attempting to grab data ('meta_value') from different rows in the same table, the names of which can only be found by reading another corresponding column ('meta_key') in the same table. All this while joining everything on the user_id common in all 3 tables.

SELECT mod_membership.uid,
       mod_membership.wp_user_id,
       mod_membership.status,
       mod_membership.last_login,
       mod_membership.membership_type,
       mod_membership.membership_expiration,
       wp_users.user_login,
       wpm_a.meta_value AS first_name
  FROM mod_membership
  JOIN wp_users ON wp_users.ID = mod_membership.wp_user_id
  JOIN wp_usermeta AS wpm_a ON wpm_a.user_id = mod_membership.wp_user_id WHERE wpm_a.meta_key = 'first_name'
  JOIN wp_usermeta AS wpm_b ON wpm_b.user_id = mod_membership.wp_user_id WHERE wpm_b.meta_key = 'last_name'

How can I get this third JOIN to work, or use another method to get these results in a single result set, grouped on user_id?

Answer 1

Exchange WHERE for AND

SELECT mod_membership.uid,

mod_membership.wp_user_id,

mod_membership.status,

mod_membership.last_login,

mod_membership.membership_type,

mod_membership.membership_expiration,

wp_users.user_login,

wpm_a.meta_value AS first_name

FROM mod_membership

JOIN wp_users ON wp_users.ID = mod_membership.wp_user_id

JOIN wp_usermeta AS wpm_a ON wpm_a.user_id = mod_membership.wp_user_id AND wpm_a.meta_key = 'first_name'

JOIN wp_usermeta AS wpm_b ON wpm_b.user_id = mod_membership.wp_user_id AND wpm_b.meta_key = 'last_name'

Answer 2

As far as I can tell, you don't need a 3rd join - it looks like you're trying to do this:

SELECT DISTINCT mod_membership.uid, mod_membership.wp_user_id,
mod_membership.status, mod_membership.last_login,
mod_membership.membership_type, mod_membership.membership_expiration,
wp_users.user_login, wpm_a.meta_value AS first_name

FROM mod_membership
INNER JOIN wp_users ON wp_users.ID = mod_membership.wp_user_id
INNER JOIN wp_usermeta AS wpm_a ON wpm_a.user_id = mod_membership.wp_user_id
WHERE (wpm_a.meta_key = 'first_name' OR wpm_a.meta_key = 'last_name')

The reason your original query isn't working is that you can only have one WHERE clause.

Answer 3

try this:

 SELECT m.uid, m.wp_user_id,m.status,
     m.last_login,m.membership_type,
     m.membership_expiration,
     u.user_login,
     f.meta_value first_name,
     l.meta_value last_name
From mod_membership m
   Join wp_users u 
      On u.ID = m.wp_user_id
   Left Join wp_usermeta f
      On f.user_id = m.wp_user_id
         And f.meta_key = 'first_name'
   Left Join wp_usermeta l
      On l.user_id = m.wp_user_id
         And l.meta_key = 'last_name'

To answer your question the reason your syntax was not working is, first of all, there can be only one where clause per sql statement. You can't add a where clause for each join, that's what the "On" cluase is for...

Secondly, the conditions in that Where clause are not applied until the final resultset has been generated, whereas join conditions are evaluated "along the way" as each intermediate result set is constructed from each successive join statement.