Selecting multiple table values with doubled joins

Question

I have a query which selects values from different tables via JOIN. But I think I have a problem now, because one table needs to join in again with a different name, but somehow it doesn't work.

An example based on a social network strucutre:

Table "users":

+--------+-----------+
| userid | username  | 
+--------------------|
| 1      | userOne   | 
| 2      | userTwo   | 
| 3      | userThree | 
+--------+-----------+

Table "posts":

+--------+---------+-------------------------------+
| postid | userid  | text                          |
+--------------------------------------------------|
| 102    | 1       | "Haha i'm User one"           |
| 103    | 1       | "And User one is the best"    |
| 104    | 3       | "I'm having fun with user two"|
+--------+---------+-------------------------------+

Table "usertags":

+--------+---------------+
| postid | tagged_userid | 
+------------------------|
| 104    | 2             | 
+--------+---------------+

This is my query:

SELECT posts.postid,
   posts.userid,
   posts.text, 
   users.username,
   IFNULL(GROUP_CONCAT(DISTINCT usertags.tagged_userid SEPARATOR ','), NULL) as 
taggedusers_id,
   IFNULL(GROUP_CONCAT(DISTINCT taggedusers.fullname SEPARATOR ','), NULL) as 
taggedusers_name, 
     FROM posts
JOIN users ON posts.userid = users.userid
LEFT JOIN usertags ON posts.postid = usertags.postid
LEFT JOIN users as taggedusers ON usertags.tagged_userid = users.userid
 GROUP BY posts.postid
 ORDER BY posts.postid DESC

And that's the result i get:

+--------+---------+---------------------------------------------------------------------+
| postid | userid  | text                          | username           | taggedusers_id |
+-----------------------------------------------------------------------|----------------|
| 102    | 1       | "Haha i'm User one"           | userOne            | NULL           |
| 103    | 1       | "And User one is the best"    | userOne            | NULL           |
| 104    | 3       | "I'm having fun with user two"| userThree          | 2
+--------+---------+-------------------------------+--------------------+----------------+

The problem: The column 'taggedusers_name' shows up, but it always shows NULL. I want it to show the usernames of the users which are tagged. Like this, but in the whole, big output

+---------------+-------------------+
| taggeduser_id | taggeduser_name   | 
+-----------------------------------|
| 2             | userTwo           | 
| 2,3           | userTwo,userThree | 
| NULL          | NULL              | 
+---------------+-------------------+

So, how is this possible? Do I need to make a multiple SELECT statement? I tried that already, but I failed at this too :/ I'd be glad for help!

Your sample data doesnt match your desire output, also the schema arent complete you have missing columns used on the query. — Juan Carlos Oropeza, Oct 25 '17 at 15:37
Unrelated, what's the point of the `IFNULL(***, NULL)`? Basically, you're saying, if it's `NULL` set it to `NULL`. Seems redundant, unless there's a benefit I'm missing. — kchason, Oct 25 '17 at 15:40

kchason · Accepted Answer · 2017-10-25T16:01:33.033

The issue is that you're referencing users again without the alias. Since users is already being implicitly INNER JOIN (see this question What is the default MySQL JOIN behaviour, INNER or OUTER?) so the taggedusers table will have to meet the conditions that the author is the same as the tagged user ID.

SELECT posts.postid,
   posts.userid,
   posts.text, 
   users.username,
   IFNULL(GROUP_CONCAT(DISTINCT usertags.tagged_userid SEPARATOR ','), NULL) as 
taggedusers_id,
   IFNULL(GROUP_CONCAT(DISTINCT taggedusers.fullname SEPARATOR ','), NULL) as 
taggedusers_name, 
FROM 
    posts
JOIN 
    users 
    ON posts.userid = users.userid
LEFT JOIN 
    usertags 
    ON posts.postid = usertags.postid
LEFT JOIN 
    users as taggedusers 
    ON usertags.tagged_userid = taggedusers.userid -- this is (I assume) what you meant
    -- ON usertags.tagged_userid = users.userid -- this is your problem
GROUP BY 
    posts.postid
ORDER BY 
    posts.postid DESC

One way to avoid this is to always alias tables; in this case you could have aliased users as 'author' or something of the like.

The reason you didn't have the same problem with the distinct IDs is that it was on a table that was joined correctly.

Thank you! You saved me lots of hours realizing this – Olli Oct 25 '17 at 16:00 — Olli, Oct 25 '17 at 16:00

Selecting multiple table values with doubled joins

1 Answers1