How to Retrieve Records Containing All of a Given Set of Values (But Potentially More) Using SQL Joins?

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

Given a set of tables with relationships, the goal is to retrieve records from one table (offers) that include a given array of values but may also have additional values.

In SQL, this can be achieved using a combination of joins, grouping, and the HAVING clause. The following query accomplishes this task:

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;

Considerations:

• The primary key of the offer table (o.id) is used for grouping to ensure that each offer is counted once.
• GROUP BY can be used to aggregate records without using DISTINCT in the count operation.
• The HAVING clause filters the grouped results to only include offers that meet the desired criteria (in this case, having two specific sports).

ActiveRecord Implementation:

def self.includes_sports(*sport_names)
  joins(:sports)
    .where(sports: { name: sport_names })
    .group('offers.id')
    .having("count(*) = ?", sport_names.size)
end

With this method, you can query for offers that include the specified sport names in ActiveRecord:

offers = Offer.includes_sports("Bodyboarding", "Surfing")

The above is the detailed content of How to Retrieve Records Containing All of a Given Set of Values (But Potentially More) Using SQL Joins?. For more information, please follow other related articles on the PHP Chinese website!