Find matching column data between two row

[tamadon]

Hi,

I want to find the matching value between two rows in the same sqlite table. For example, if I have the following table:


rowid, col1, col2, col3
----- ---- ---- ----
1 5 3 1
2 3 6 9
3 9 12 5

So comparing row 1 and 2, I get the value 3.
Row 2 and 3 will give 9.
Row 3 and 1 will give 5.

There will always be one and only one matching value between any two rows in the table.

What it the correct sqlite query for this?

 

[tamadon]

Got the answer somewhere. Here it is in any case (for 8 columns). "cards" is the table name.

select t1.rowid as r1, t2.rowid as r2, t2.col as matchvalue from cards t1 join
(
  select rowid, col1 col from cards where rowid=3 union all
  select rowid, col2 from cards where rowid=3 union all
  select rowid, col3 from cards where rowid=3 union all
  select rowid, col4 from cards where rowid=3 union all
  select rowid, col5 from cards where rowid=3 union all
  select rowid, col6 from cards where rowid=3 union all
  select rowid, col7 from cards where rowid=3 union all
  select rowid, col8 from cards where rowid=3
) t2
on t2.col in (t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t1.col6, t1.col7, t1.col8)
and t1.rowid = 1