Need to do a MySQL OUTER JOIN

Question

I have a MYSQL query that works just right except that I want to perform an OUTER join on two statements so that they display rows of the first table that does not need to have corresponding rows in the second table. The Query is as follows

SELECT wp_usermeta.user_id,
                    m9.id as group_id,
                    wp_usermeta.meta_value as nickname,
                    m2.meta_value as abbreviation,
                    m3.meta_value as name,
                    m4.meta_value as games,
                    m5.description as description,
                    m6.slug as slug,
                    m7.date_created as date_created,
                    m8.meta_value as total_member_count,
                    m10.status as status,
                    m11.membership_id as type
            FROM wp_usermeta 
            INNER JOIN wp_usermeta as m2 ON wp_usermeta.user_id = m2.user_id
            INNER JOIN wp_usermeta as m3 ON wp_usermeta.user_id = m3.user_id
            INNER JOIN wp_usermeta as m4 ON wp_usermeta.user_id = m4.user_id
            INNER JOIN wp_bp_groups as m5 ON wp_usermeta.user_id = m5.creator_id
            INNER JOIN wp_bp_groups as m6 ON wp_usermeta.user_id = m6.creator_id
            INNER JOIN wp_bp_groups as m7 ON wp_usermeta.user_id = m7.creator_id
            INNER JOIN wp_bp_groups as m9 ON wp_usermeta.user_id = m9.creator_id
            INNER JOIN wp_bp_groups_groupmeta as m8 ON m9.id = m8.group_id
          #  INNER JOIN wp_2_pmpro_memberships_users as m10 ON wp_usermeta.user_id = m10.user_id
          #  INNER JOIN wp_2_pmpro_memberships_users as m11 on wp_usermeta.user_id = m11.user_id
            WHERE (wp_usermeta.meta_key = 'nickname' AND 
                    m2.meta_key = 'abbreviation' AND
                    m3.meta_key = 'name' AND
                    m4.meta_key = 'games' AND
                    m8.meta_key = 'total_member_count' AND
                    m10.status  = 'active' AND
                    m11.status  = 'active'
                    )

According to this SO thread I have tried to use the format

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id

to change the two lines marked with a # above to

            INNER JOIN (
                    SELECT * FROM wp_usermeta
                    LEFT JOIN wp_2_pmpro_memberships_users ON wp_usermeta.user_id = wp_2_pmpro_memberships_users.user_id
                    UNION
                    SELECT * FROM wp_usermeta
                    RIGHT JOIN wp_2_pmpro_memberships_users ON wp_usermeta.user_id = wp_2_pmpro_memberships_users.user_id
                )

But it doesn't seem to work. MySQL throws an error

1248 - Every derived table must have its own alias

My SQL Query knowledge is Basic and limited to simple DB Queries. Any help would be appreciated.

Do you need a FULL OUTER JOIN? I don't think so. Isn't it just a case of replacing the word 'INNER' with 'LEFT' ?? Oh and changing the word 'WHERE' to 'AND' — Strawberry, Jan 28 '16 at 07:43
Yes I do Need a FULL OUTER JOIN and I tired changing INNER to LEFT earlier. Did not give me the results I needed. — Ronald Fernandes, Jan 28 '16 at 07:48

score 1 · Answer 1 · answered Jan 28 '16 at 08:00

1

The basic way of finding which ids you have in one table that you do not have in another table is

Select * from t1 where t1.commonid not in(select commonid from t2);

This works, but scales terribly as your tables grow. so a better approach will be

Select t1.* 
FROM t1 left join t2 on t1.commonid = t2.commonid
where t2.id is null

The left join gives you all rows from t1 with corresponding rows from t2, rows from t1 with no corresponding rows in t2 returns null for all t2 values. so just use any t2 field with "not null" set.

answered Jan 28 '16 at 08:00

MortenSickel

2,118
4
26
44

Any evidence for this? – Strawberry Jan 28 '16 at 08:19
Evidence for what? that a subselect scales badly or that the left join Works? – MortenSickel Jan 28 '16 at 08:27
That alternatives to LEFT JOIN, such as IN(SELECT..), etc scale less well - although in fact I *think* it misses the point of the question. – Strawberry Jan 28 '16 at 08:34

score 1 · Accepted Answer · answered Jan 28 '16 at 08:38

It seems to me that you're simply after this:

         SELECT wp_usermeta.user_id,
                m9.id as group_id,
                wp_usermeta.meta_value as nickname,
                m2.meta_value as abbreviation,
                m3.meta_value as name,
                m4.meta_value as games,
                m5.description as description,
                m6.slug as slug,
                m7.date_created as date_created,
                m8.meta_value as total_member_count,
                m10.status as status,
                m11.membership_id as type
        FROM wp_usermeta 
        INNER JOIN wp_usermeta as m2 ON wp_usermeta.user_id = m2.user_id
        INNER JOIN wp_usermeta as m3 ON wp_usermeta.user_id = m3.user_id
        INNER JOIN wp_usermeta as m4 ON wp_usermeta.user_id = m4.user_id
        INNER JOIN wp_bp_groups as m5 ON wp_usermeta.user_id = m5.creator_id
        INNER JOIN wp_bp_groups as m6 ON wp_usermeta.user_id = m6.creator_id
        INNER JOIN wp_bp_groups as m7 ON wp_usermeta.user_id = m7.creator_id
        INNER JOIN wp_bp_groups as m9 ON wp_usermeta.user_id = m9.creator_id
        INNER JOIN wp_bp_groups_groupmeta as m8 ON m9.id = m8.group_id
      #  LEFT JOIN wp_2_pmpro_memberships_users as m10 
                ON wp_usermeta.user_id = m10.user_id
               AND m10.status  = 'active' 

      #  LEFT JOIN wp_2_pmpro_memberships_users as m11 
                ON wp_usermeta.user_id = m11.user_id
               AND m11.status  = 'active'

        WHERE (wp_usermeta.meta_key = 'nickname' AND 
                m2.meta_key = 'abbreviation' AND
                m3.meta_key = 'name' AND
                m4.meta_key = 'games' AND
                m8.meta_key = 'total_member_count' 
                )

That's perfect. The results come up exactly how I need it! I don't understand tough, I had done the LEFT JOIN the same way except I had not added the AND m10.status = 'active' at the end. How did this addition make it work? — Ronald Fernandes, Jan 29 '16 at 03:50
With the subtle exception of 'WHERE x IS NULL', placing conditions on an outer-joined table in the WHERE clause effectively renders that OUTER JOIN as an INNER JOIN. Which is why you need to include those conditions within the join instead. These are not a full joins, just a regular series of outer joins. — Strawberry, Jan 29 '16 at 07:36

Need to do a MySQL OUTER JOIN

1248 - Every derived table must have its own alias

2 Answers2