Find matching column data between two row

tamadon

Active Member
Licensed User
Longtime User
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

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

B4X:
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
 
Upvote 0
Top