SQL one-to-many match the one side by ALL in many side

sql sql-match-all
James Massey picture James Massey · Oct 16, 2008 · Viewed 10k times · Source

In the following one to many

CREATE TABLE source(id int, name varchar(10), PRIMARY KEY(id));
CREATE TABLE params(id int, source int, value int);

where params.source is a foreign key to source.id

INSERT INTO source values(1, 'yes');
INSERT INTO source values(2, 'no');

INSERT INTO params VALUES(1,1,1);
INSERT INTO params VALUES(2,1,2);
INSERT INTO params VALUES(3,1,3);

INSERT INTO params VALUES(4,2,1);
INSERT INTO params VALUES(5,2,3);
INSERT INTO params VALUES(6,2,4);

If i have a list of param values (say [1,2,3]), how do I find all the sources that have ALL of the values in the list (source 1, "yes") in SQL?

Thanks

Answer

Bill Karwin picture Bill Karwin · Oct 16, 2008 
SELECT s.*
FROM source AS s
 JOIN params AS p ON (p.source = s.id)
WHERE p.value IN (1,2,3)
GROUP BY s.id
HAVING COUNT(DISTINCT p.value) = 3;

You need the DISTINCT because your params.value is not prevented from having duplicates.

Related questions

How to filter SQL results in a has-many-through relation

Assuming I have the tables student, club, and student_club: student { id name } club { id name } student_club { student_id club_id } I want to know how to find all students in both the soccer (30) and baseball (50) club. While this …

Read More
Select values that meet different conditions on different rows?

This is a very basic query I can't figure out.... Let's say I have a two column table like this: userid | roleid --------|-------- 1 | 1 1 | 2 1 | 3 2 | 1 I want to get all distinct userids that have roleids 1, 2 AND 3. Using the above example, the …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More