Many-to-one Subselection in single query

Question

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2.

To create a simple example, lets say T1 is Cars, and T2 is a table of imperfections. So, a car can have 0..N imperfections, and we store these imperfections in T2 as integers.

I would like to select * from only those cars in Cars that contain imperfections i1 AND i2.

Performing an OR instead is pretty easy:

SELECT * FROM cars AS T1 
  WHERE EXISTS (
    SELECT imperfection FROM Imperfections as T2 
      WHERE T1.uid = T2.uid AND (imperfection = 1 OR imperfection = 2)
  );

I've been trying set logic using intersection, but at this point, I'm wondering if I'm over complicating it.

Answer 1

You could have two separate exists clauses:

SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 1
    )
    AND EXISTS
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 2
    );

This means two left semi joins to your Imperfections table, but if you have an index on UID and Imperfection, those should be trivial.

There are two advantages to using WHERE EXISTS over JOIN in this case. The first is that a left semi-join stops after the first match, so it can be quicker than a join (which needs to go through all records). The second is that if you somehow don't have good data constraints and have two records in Imperfections with the same UID and Imperfection, you could end up with duplicate rows back in your main query.

Answer 2

The easiest shortest way to go is

SELECT c.* 
FROM 
    cars AS c
    JOIN Imperfections as i1 ON c.uid = i1.uid AND i1.imperfection = 1
    JOIN Imperfections as i2 ON c.uid = i2.uid AND i2.imperfection = 2
;

The (INNER) JOINs test for existence themselves.

This holds if (uid, imperfection) is UNIQUE, which would be plausible here, but has not been defined. There could be subtypes of imperfections ... then you'd need a DISTINCT or GROUP BY. USING (uid) would be even shorter than ON c.uid = i1.uid. Imperfections would go into a WHERE clause then.

The next idea would be the same that Kevin describes in the his answer, so I won't include it here.

Answer 3

SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT 1 FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection IN(1,2)
        HAVING COUNT(DISTINCT imperfection) =2
     )