How to return a row where joins ALL exist SQL

Question

How do I return a row (preferably in ANSI SQL) when the joined table has ALL matching rows on my on?

Example of the wrong behaviour:

SELECT table1.*
    , whatever
FROM   table1
      INNER JOIN table2 ON table2.whateverrelation = table1.whateverrelation
                       AND table2.matching IN(1, 2);

will return every table1 row with a matching relation on table2 which is 1 OR 2 (i.e. will return a table1 row with one or more matching fields.

How might I get something that returns only the table1 rows which have table2 rows related and whose matching are BOTH 1 and 2 (so an AND rather than an OR, so there would have to be at least one table2 row with matching = 1 and at least one with matching = 2, so it would not show any table1 row without both matches)?

I'd rather not do two joins to the same table, I'm aware I could just have a left join twice, one to match 1 and one to match 2, but I don't know beforehand how many matches I'll need.

Cheers

Answer 1

You coud use:

SELECT table1.col1, ....
FROM table1 
INNER JOIN table2 
  ON table2.whateverrelation = table1.whateverrelation 
GROUP BY  table1.col1, ....
HAVING SUM(table2.matching = 1) > 0
   AND SUM(table2.matching = 2) > 0;