How to Enforce Conditions Across Multiple Rows When Joining Tables in SQL?

Employing SQL to Enforce Conditions Across Multiple Rows in a Join

Considering your initial inquiry regarding enforcing conditions across multiple rows in a join, let's elaborate on the available techniques.

Testing Different Rows

1A. Utilizing 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')

1B. Employing 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')

1C. Leveraging 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'

Aggregating Rows

2A. Utilizing 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

2B. Employing 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

Note: This approach utilizes MySQL-specific extensions and is inefficient compared to others.

The above is the detailed content of How to Enforce Conditions Across Multiple Rows When Joining Tables in SQL?. For more information, please follow other related articles on the PHP Chinese website!