A sqlite database has the two following tables:
table1 table2
--------- ---------
f1 | f2 e | f
--------- ---------
0 | 1 0 | 1
2 | 3 0 | 2
1 | 4 0 | 3
2 | 1 1 | 4
0 | 2 1 | 0
3 | 0 1 | 3
Now I would like to select those pairs of elements (f1,f2) from table1 where both f1 and f2 are among those f in table2, to which e=0.
This can be achieved with
SELECT f1, f2 FROM table1
WHERE f1 IN (SELECT f FROM table2 WHERE e=0)
AND f2 IN (SELECT f FROM table2 WHERE e=0)
The result, as expected is
2 3
2 1
but there is a repetition in the query above, which I thought could be eliminated by making
SELECT f1, f2 FROM table1
WHERE f1, f2 IN (SELECT f FROM table2 WHERE e=0)
but this gives me a syntax error, and
SELECT f1, f2 FROM table1
WHERE f1 AND f2 IN (SELECT f FROM table2 WHERE e=0)
doesn't give anything.
Is there a way to get it without repetition?
(My motivation is that this is to be accessed from a python program where the constants constraining the query are passed as arguments. With the code I'm using, I have to make tuples twice as big, with second half equal to the first, and I guess this will be more prone to errors, specially when the query is a bit more complicated. It's working, but I'd like to improve... I'm not including any of that python code because I don't think it's really relevant, but I could do that too.)
If the combination of f1, f2 is unique in table1, you can join the tables, GROUP BY f1, f2 and set the condition in the HAVING clause:
SELECT t1.f1, t1.f2
FROM table1 t1 INNER JOIN table2 t2
ON t2.f IN (t1.f1, t1.f2)
WHERE t2.e = 0
GROUP BY t1.f1, t1.f2
HAVING COUNT(*) = 2
See the demo.