How to Select Offers Containing All Specified Sports Using SQL?

SQL where joined set must contain all values but may contain more

In relational database management systems (RDBMS), it is often necessary to retrieve data based on specific conditions. In this particular scenario, the task is to select rows from the offers table that include all of the sports specified in a given array, while potentially containing additional sports.

To achieve this outcome, the following SQL query can be utilized:

SELECT o.*
FROM   sports        s
JOIN   offers_sports os ON os.sport_id = s.id
JOIN   offers        o  ON os.offer_id = o.id
WHERE  s.name IN ('Bodyboarding', 'Surfing')
GROUP  BY o.id
HAVING count(*) = 2;

Breaking down the query:

• The JOIN statements establish relationships between the sports, offers_sports, and offers tables.
• The WHERE clause filters the sports table based on the specified sport names.
• The GROUP BY statement groups the results by the offer_id column from the offers table.
• The HAVING clause checks if each group contains exactly two unique sport names.

In summary, this SQL query retrieves offers that meet the specified criteria, ensuring that all required sports are present without excluding offers that include additional sports.

The above is the detailed content of How to Select Offers Containing All Specified Sports Using SQL?. For more information, please follow other related articles on the PHP Chinese website!