SQL Select from many-to-one

I have two tables containing a one-to-many relationship. How should I select the “one” side given information for the “many” side? I’m using SQLite.

Let’s say our first table, T1, is about cars and the second table, T2, is about the imperfections in each car.

Let’s say we can describe the imperfections with an integer.

So we have:

CREATE TABLE T1 (
       id INTEGER PRIMARY KEY,
       [Other columns]
);

CREATE TABLE T2 (
       carId INTEGER REFERENCES T1(id),
       imperfection INTEGER
);

We have data about a few cars and each one has a number of imperfections.

Given a list of integers representing imperfections, how can I select the cars that have exactly this set of imperfections? For example, let’s say I have [1,5,7] as the list of imperfections, how can I find out that matches car 19?

If it’s helpful, I expect that there is only one match for each unique set of imperfections.

This question is similar to that in a DBA Stack Exchange question, however in the previous question the number of imperfections were known. In this question, I do not know the number of imperfections.

Answer

You can use conditional aggregation. Assuming you have no duplicates:

select carid
from t2
group by carid
having sum(case when imperfection in (1, 5, 7) then 1 else 0 end) = 3;

This finds only cars that have exactly 1, 5, and 7. If you wanted any car that had these — and perhaps others — you can simplify this to:

select carid
from t2
where imperfection in (1, 5, 7)
group by carid
having count(*) = 3;