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

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 … Read more

Select values that meet different conditions on different rows?

Ok, I got downvoted on this so I decided to test it: CREATE TABLE userrole ( userid INT, roleid INT, PRIMARY KEY (userid, roleid) ); CREATE INDEX ON userrole (roleid); Run this: <?php ini_set(‘max_execution_time’, 120); // takes over a minute to insert 500k+ records $start = microtime(true); echo “<pre>\n”; mysql_connect(‘localhost’, ‘scratch’, ‘scratch’); if (mysql_error()) { … Read more