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

by

Group by offer.id, not by sports.name (or sports.id):

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;

Assuming the typical implementation:

  • offer.id and sports.id are defined as primary key.
  • sports.name is defined unique.
  • (sport_id, offer_id) in offers_sports is defined unique (or PK).

You don’t need DISTINCT in the count. And count(*) is even a bit cheaper, yet.

Related answer with an arsenal of possible techniques:

Added by @max (the OP) – this is the above query rolled into ActiveRecord:

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(*) = ?", sport_names.size)
  end
end

More Related Contents:

  1. PostgreSQL where all in array
  2. PostgreSQL GROUP BY different from MySQL?
  3. Best way to count rows by arbitrary time intervals
  4. LEFT OUTER JOIN in Rails 4
  5. Perform this hours of operation query in PostgreSQL
  6. Find records where join doesn’t exist
  7. Rails 4 LIKE query – ActiveRecord adds quotes
  8. Prepared Statement on Postgresql in Rails
  9. Select rows which are not present in other table
  10. Optimize GROUP BY query to retrieve latest row per user
  11. How to implement a many-to-many relationship in PostgreSQL?
  12. Aggregate columns with additional (distinct) filters
  13. Check if value exists in Postgres array
  14. How to use ANY instead of IN in a WHERE clause?
  15. Is there a shortcut for SELECT * FROM?
  16. sql statement error: “column .. does not exist”
  17. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  18. sql ORDER BY multiple values in specific order?
  19. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  20. Sorting null values after all others, except special
  21. Reference alias in WHERE clause
  22. NOT NULL constraint over a set of columns
  23. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  24. Why postgres returns unordered data in select query, after updation of row?
  25. postgresql – sql – count of `true` values
  26. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  27. Join multiple tables with active records
  28. Rails find record with zero has_many records associated [duplicate]
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment