Select only records with different values

Databases
1

Is there a way to select records where certain fields are different?

id  | file  | idx   | values
----|-------|-------|----------------------
 1  | 1     | 101   | 1,3,7,11,23,11
 2  | 2     | 101   | 1,3,7,11,23,11
 3  | 3     | 101   | 0,4,8,60,20,11
 4  | 1     | 211   | 12,11,23
 5  | 2     | 211   | 12,0,23
 6  | 3     | 211   | 12,0,23
 7  | 1     | 300   | 1
 8  | 2     | 300   | 0
 9  | 3     | 300   | 0

I want to select only file=1 and file=2 and look the values in the values column are different. So, a result could be sometime like this:

id  | file  | idx   | values
----|-------|-------|----------------------
 4  | 1     | 211   | 12,11,23
 5  | 2     | 211   | 12,0,23
 7  | 1     | 300   | 1
 8  | 2     | 300   | 0

I just can’t wrap my head around this problem. I think it has something to do with INNER JOIN or OUTER JOIN.

2

Quick guess:

select * from thetable a, thetable b where a.file <> b.file and a.idx = b.idx and a.values <> b.values;

3

And restrict it to file = 1 and file = 2, right?

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);
4

Yeah, I changed the SQL to something like this:

select * from  thetable a, thetable b
where a.file <> b.file and a.idx = b.idx and a.values <> b.values and
 (  (a.file = ? and b.file = ?) OR   (a.file = ? and b.file = ?) )

In a PreparedStatement I used the files IDs. It looks kinda weird here. But not when I use “FileA, FileB, FileB, FileA”
But your syntax looks a lot cleaner. I will update it. I never used “in” in a SQL statement.

And I think I get it actually to work.

So, 1000x thanks to Kem and Jeff :smiley: