sqlite difference between rows

Question

In SQLite I have a collection of records and I want to only show the records with specific differences.

The table has something like the following values: file | idx | values ------|-------|---------------------- 1 | 101 | 1,3,7,11,23,11 2 | 101 | 1,3,7,11,23,11 3 | 101 | 0,4,8,60,20,11 1 | 211 | 12,11,23 2 | 211 | 12,0,23 3 | 211 | 12,0,23 1 | 300 | 1 2 | 300 | 0 3 | 300 | 0

I want to be able to select two different fileIDs, and compare them. I mean, I want to examine only records with (file = 1 AND file = 2)

What I cant to get back as a result is a collection of records that are not the same:
file | idx | values ------|-------|---------------------- 1 | 211 | 12,11,23 2 | 211 | 12,0,23 1 | 300 | 1 2 | 300 | 0

score 0 · Accepted Answer · answered Feb 22 '17 at 21:34

So you do not want rows for which another row with the same idx and values values exists:

SELECT *
FROM MyTable
WHERE file IN (1, 2)
  AND NOT EXISTS (SELECT *
                  FROM MyTable AS T2
                  WHERE file IN (1, 2)
                    AND file   <> MyTable.file
                    AND idx    =  MyTable.idx
                    AND values =  MyTable.values);

score 0 · Answer 2 · answered Feb 22 '17 at 21:35

I just recieved an answer in another forum. This seems to work:

select * from thetable a, thetable b where a.file <> b.file and a.idx = b.idx and a.values <> b.values and a.file in (1, 2) and b.file in (1, 2);

Of course I change certain values as variables in a prepared statement. But it did the trick

sqlite difference between rows

2 Answers2