How to Select Offers Containing All of a Given Set of Sports (But Possibly More) in SQL?

SQL: WHERE Joined Set Must Contain All Values But May Contain More

In SQL, the "WHERE JOINED SET" clause ensures that a joined table includes specific values in its result set. However, the joined table may also include additional values that are not part of the condition. This concept can be challenging to implement in practice, especially when determining which values to include in the condition.

Consider the following example:

Scenario:

You have three tables: offers, sports, and offers_sports, which represent offers, sports, and the relationship between them. You want to select offers that include a given array of sport names. The offers must contain all of the sports but may also include additional sports.

Data:

offers
| id | name |
| --- | ---- |
| 1 | light |
| 2 | medium |
| 3 | all |
| 4 | extreme |

sports
| id | name |
| --- | ---- |
| 1 | Yoga |
| 2 | Bodyboarding |
| 3 | Surfing |
| 4 | Parasailing |
| 5 | Skydiving |

offers_sports
| offer_id | sport_id |
| --- | ---- |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 3 |
| 4 | 4 |
| 4 | 5 |

Desired Result:

Given the array ["Bodyboarding", "Surfing"], the query should return the offers medium and all as they contain both of the specified sports. The offer light should not be returned as it does not include both sports.

Incorrect Query:

The following query, which groups by sport name and ensures that exactly two sports are included in each offer, returns no results:

SELECT "offers".* 
FROM "offers" 
INNER JOIN "offers_sports" ON "offers_sports"."offer_id" = "offers"."id"     
INNER JOIN "sports" ON "sports"."id" = "offers_sports"."sport_id" 
  WHERE "sports"."name" IN ('Bodyboarding', 'Surfing') 
GROUP BY sports.name 
HAVING COUNT(distinct sports.name) = 2;

Solution:

The correct query groups by offer ID instead of sport name and checks the count of distinct sports included in each offer using COUNT(DISTINCT):

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;

This query will return the offers medium and all as they both include both of the specified sports.

ActiveRecord Implementation:

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
  def self.includes_sports(*sport_names)
    joins(:sports)
      .where(sports: { name: sport_names })
      .group('offers.id')
      .having("COUNT(DISTINCT sports.name) = ?", sport_names.size)
  end
end

The above is the detailed content of How to Select Offers Containing All of a Given Set of Sports (But Possibly More) in SQL?. For more information, please follow other related articles on the PHP Chinese website!