SELECT data from relational DB tables

Databases
1

Hi,

I have three MySQL DB tables in the following structures:

users

+---------+
| user_id |
+---------+
| 1       |
+---------+
| 2       |
+---------+

tags

+---------+
| tag_id  |
+---------+
| 1       |
+---------+
| 2       |
+---------+

user_tags

+---------+-----------+--------+
| id      | user_id   | tag_id |
+---------+-----------+--------+
| 1       | 1         | 1      |
+---------+-----------+--------+
| 2       | 1         | 2      |
+---------+-----------+--------+
| 3       | 2         | 1      |
+---------+-----------+--------+

Each user may have multiple tags assigned to them.

I want to display the user_id and tag_id(s) for all users who are tagged with tag_id = 1. The desired output is the following:

+---------+------------------+
| user_id | tags             |
+---------+------------------+
| 1       | 1,2              |
+---------+------------------+
| 2       | 1                |
+---------+------------------+

I tried the following query:

SELECT users.user_id, GROUP_CONCAT(tags.tag_id SEPARATOR ',') AS tags
FROM users
LEFT JOIN user_tags ON users.user_id = user_tags.user_id
LEFT JOIN tags ON tags.tag_id = user_tags.tag_id
WHERE tags.tag_id IN (1)
GROUP BY users.user_id

which displays:

+---------+------------------+
| user_id | tags             |
+---------+------------------+
| 1       | 1                |
+---------+------------------+
| 2       | 1                |
+---------+------------------+

If you compare this table to the desired table, you will notice that Tag 2 is not displayed for User 1, which should be.

Any ideas how to modify my query to get the desired output?

Thanks.

2

Yes. Include tag 2 in the WHERE clause

WHERE tags.tag_id IN (1)
3

Thanks, but that means to select users who are assigned with tag_id = 1 OR tag_id = 2, which, as in my test, also selects a user who is assigned with tag_id = 2 but not with tag_id = 1.

I want to select users assigned with tag_id = 1, and I want to display all tags for each user. (I can do this using one extra query for each user with something like get_user_tags(user_id), but I am trying to do it with a single query if possible.)

4

You can’t have it both ways with the query you’re using. The group_concat only works with the records that are filtered through the where clause. You’ll need to use a sub-query

SELECT users.user_id
     , tagList
  FROM users
  LEFT JOIN (SELECT user_tags.user_id
				  , GROUP_CONCAT(tags.tag_id SEPARATOR ',') AS tagList
			   FROM user_tags ON users.user_id = user_tags.user_id
			  INNER JOIN tags ON tags.tag_id = user_tags.tag_id
			  GROUP BY user_tags.user_id) SQ ON SQ.user_id = users.user_id
 INNER JOIN user_tags ON users.user_id = user_tags.user_id
 WHERE user_tags.tag_id = 1

A couple notes:

  • I changed all the LEFT JOINS to INNER JOINS because you’re using a WHERE clause on the joined table and are looking for a specific value, which requires a match, hence an INNER JOIN.
  • I removed the second join on the main clause since you’re just looking for the tag_id. If you’re not pulling an actual value from the tags table, you can remove the second join from the second query. I left it there since I thought you might be pulling a different value from the tags table (like tag name or description) which would need the second join. But if you’re just filtering by tag_id, the second join isn’t needed.
  • I changed the IN to = since you’re only looking for one value. Depending on the indexing on the field, a table scan can be avoided if using the equal operator, which improves performance.
1 Like
5

Thank you very much! It worked perfectly.

The code after “FROM user_tags” on the same line was unnecessary, so I removed it.

Also thanks for your comments.

  • I’ve read about left vs. inner joins a lot but still don’t have a perfect understanding of each.
  • I get tag names from the tags table in my actual code.
  • I used IN (…) because it may have multiple values to filter. Ex: List all users who are assigned with tags 2, 3 AND 5, etc…
6

Oops. Started with your code and forgot to remove that. Sorry!

Unfortunately IN doesn’t work that way. IN is a replacement for OR, not for AND. If you’re looking for AND, that’s a whole other ball of wax.

7

but don’t fall into the trap of trying this –

WHERE user_tags.tag_id = 1 
  AND user_tags.tag_id = 2 
  AND user_tags.tag_id = 3
1 Like
8

I might have wrongly phrased my last example, but IN (…) is what works fine in my case, thanks again.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.