CODEWITHSUNDEEP
sqlitejoinsql-in
amrsa
amrsa

Reputation: 245

Select fields from a table which satisfy condition in another table

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.)

Upvotes: 1

Views: 760

Answers (1)

forpas
forpas

Reputation: 164139

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.

Upvotes: 1

Related Questions