How to Select Users with Both 'tag1' and 'tag2' in SQL Using JOINs?

Conditions Across Multiple Rows in SQL JOINs

Problem: Applying conditions across multiple rows in a JOIN to retrieve matching records.

Objective: Select users who have both 'tag1' and 'tag2' tags.

Application

There are two main approaches to solve this problem:

1. Testing Individual Rows

A. EXISTS:

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')

B. Sub-queries:

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') 

C. JOINs:

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

A. COUNTs:

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

B. String Processing:

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 

Considerations

• Testing Individual Rows: Suitable for small datasets, but performance may suffer for large amounts of data.
• Aggregating Rows: More scalable performance-wise, but has limitations if duplicates can exist within the aggregate.
• Database Compatibility: String processing techniques may require database-specific extensions.

The above is the detailed content of How to Select Users with Both 'tag1' and 'tag2' in SQL Using JOINs?. For more information, please follow other related articles on the PHP Chinese website!