CODEWITHSUNDEEP
sqlsqlite
user2160982
user2160982

Reputation: 189

SQLite check if subquery gives same answers as other subquery

I need to construct query like this:

SELECT * FROM table1 AS t1 WHERE (
    (SELECT column2 FROM table1 WHERE column1=t1.column1)!=(SELECT column2 FROM table1 WHERE column1=1)
)

But the problem is that SQLite checks only first results of subqueries.

I mean that if SELECT column2 FROM table1 WHERE column1=t1.column1 gives following results: (1,2,3) and SELECT column2 FROM table1 WHERE column1=1 gives (1,2,3,4) SQLite will check only 1!=1, not (1,2,3)!=(1,2,3,4).

I need to filter rows where prevoius two queries give only same rows (i.e. (1,2,3) from the first and (1,2,3) from the second)

The example of my table:

id | column1 | column2 | ...
1  |       1 |       1 | ...
2  |       1 |       2 | ...
3  |       1 |       3 | ...
4  |       2 |       1 | ...
5  |       2 |       2 | ...
6  |       2 |       3 | ...
7  |       2 |       4 | ...
8  |       3 |       1 | ...
9  |       3 |       2 | ...
10 |       3 |       3 | ...

And I need to get rows from id=4 to id=7 because it has 7 | 2 | 4 | ... row (for one column1 value there are not same column2 values as in column1)

Upvotes: 0

Views: 212

Answers (1)

CL.
CL.

Reputation: 180091

You could try compound select statements: subtract the subqueries from each other and check if any row is left:

SELECT *
FROM table1 AS t1
WHERE EXISTS (SELECT column2 FROM table1 WHERE column1=t1.column1
              EXCEPT
              SELECT column2 FROM table1 WHERE column1=1)
   OR EXISTS (SELECT column2 FROM table1 WHERE column1=1
              EXCEPT
              SELECT column2 FROM table1 WHERE column1=t1.column1)

Alternatively, do a similar operation with individual rows: the subqueries are different if some row does not have a counterpart in the other subquery:

SELECT *
FROM table1 AS t1
WHERE EXISTS (SELECT column2
              FROM table1 AS t2
              WHERE column1 = t1.column1
                AND NOT EXISTS (SELECT 1
                                FROM table1 AS t3
                                WHERE column2 = t2.column2
                                  AND column1 = 1))
   OR EXISTS (SELECT column2
              FROM table1 AS t2
              WHERE column1 = 1
                AND NOT EXISTS (SELECT 1
                                FROM table1 AS t3
                                WHERE column2 = t2.column2
                                  AND column1 = t1.column1))

(The second query might be more efficient if you have the requisite indexes.)

Upvotes: 2

Related Questions