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

Question

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

Bookrest

I hope someone else understands this better than I do... you might want to consider re-wording it. – Synchromesh

Answer 1

Edit Modified to handle case where there can be multiple occurances of the value for a given source.

Try this:

SELECT
    *
FROM
    source
WHERE
    (
        SELECT COUNT(DISTINCT value)
        FROM params
        WHERE params.source = source.id
          AND params.value IN (1, 2, 3)
    ) = 3

You can rewrite it to a GROUP BY as well:

SELECT
    source.*
FROM
    source
    INNER JOIN params ON params.source = source.id
WHERE
    params.value IN (1, 2, 3)
GROUP BY
    source.id,
    source.name
HAVING
    COUNT(DISTINCT params.value) = 3

Tergiversate

I'd definitively go for the group by syntax. Much more expressive and less painful to look at. – Higginbotham

what if there was more than one record in params with the same value for the same source? – Jericajericho

that is a good question Russ. I guess James need to tell us if that is true or not. – Tergiversate

Well, DISTINCT is never my first solution to anything, and in his case, a setup I've seen many times, having the same value more than once is atypical. But I've modified the solution accordingly. – Tergiversate

Providing you know the number of distinct values in params table, you could drop the params.values IN (1,2,3) in the WHERE clause above, and perhaps use an int variable to match the distinct count against – Jericajericho

I know this question is really old, but THANK YOU for putting DISTINCT in there - made it clear how to solve this with my situation, which had duplicates all over the place. – Darceldarcey

Answer 2

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.

Breakage

This was the most elegant, before lassevk implemented the same :) – Randallrandan