How to Select Users with Multiple Tags Using SQL Joins?

How to Implement SQL Conditions on Multiple Rows in a Join

The issue presented revolves around selecting users who possess two specific tags from a join operation. While the example provided using IN returns users with either tag, the desired outcome is to retrieve users who possess both tags. To accomplish this, leveraging the ALL keyword is inappropriate.

Solutions

1. Testing Different Rows

• EXISTS Subqueries: Check for existence of rows matching conditions in multiple rows.

SELECT * 
FROM users
WHERE 
  EXISTS (SELECT * FROM tags WHERE user_id = users.id AND name ='tag1') 
  AND
  EXISTS (SELECT * FROM tags WHERE user_id = users.id AND name ='tag2')

• Sub-queries: Select users matching conditions in multiple rows.

SELECT * 
FROM users
WHERE 
  id IN (SELECT user_id FROM tags WHERE name ='tag1') 
  AND
  id IN (SELECT user_id FROM tags WHERE name ='tag2') 

• JOINs: Create multiple joins for each tag condition.

SELECT u.* 
FROM users u 
INNER JOIN tags t1 ON u.id = t1.user_id
INNER JOIN tags t2 ON u.id = t2.user_id
WHERE 
  t1.name = 'tag1' 
  AND t2.name = 'tag2'

2. Aggregating Rows

• COUNTs (Requires Data Protection): Count aggregate rows to ensure the presence of both tags.

SELECT users.id, users.user_name 
FROM users INNER JOIN tags ON users.id = tags.user_id
WHERE tags.name IN ('tag1', 'tag2')
GROUP BY users.id, users.user_name
HAVING COUNT(*) = 2

• String Processing (Database-Specific): Concatenate tag names and check for presence of specific tags using database-specific functions.

(MySQL Specific)
SELECT user.id, users.user_name, GROUP_CONCAT(tags.name) as all_tags
FROM users INNER JOIN tags ON users.id = tags.user_id
GROUP BY users.id, users.user_name
HAVING FIND_IN_SET('tag1', all_tags) > 0 AND
       FIND_IN_SET('tag2', all_tags) > 0 

The above is the detailed content of How to Select Users with Multiple Tags Using SQL Joins?. For more information, please follow other related articles on the PHP Chinese website!