CODEWITHSUNDEEP
sqldatabaset-sql
Quackerjack
Quackerjack

Reputation: 83

Join tables based on set of values in distinctive columns in any order

I am facing a problem at work I just have too little understanding of SQL, or databases in general for that matter, for, to even know how to search.

Hence, after countless 'views' at StackOverflow questions, here is my first own one.

I have two tables, which have no other relation to each other, other than the same set of columns (I called them item_1 to item_3) with values in them in random order. I need to join these tables, but I can not just join them on each column, as I am looking for the same set of items in any order. The set needs to be the same in both tables (no item more, no less), but the position is irrelevant.

Here are some dummy tables to (hopefully) explain what I mean:

table1

user_id | use_name | item_1 | item_2 | item_3
--------+----------+--------+--------+--------
 1      | Tim      | A      | B      | NULL
 2      | Tom      | NULL   | NULL   | C
 3      | Sam      | A      | NULL   | NULL

table2

role     | item_1 | item_2 | item_3
---------+--------+--------+--------
type1    | A      | NULL   | B
type2    | A      | B      | C
type3    | A      | NULL   | NULL

I am looking for a select / join which produces a table like this:

user_name | role
----------+------
Tim       | type1
Sam       | type3

I tried my luck with a permutation table, but as in the real scenario we are talking about not 3 but 10 columns to consider, this seems not the best solution. I am currently trying to achieve something useful with pivot / unpivot, but without any results so far.

I would even be very happy with just a link to an article. Or even a distinctive name for my problem I could google for :)

Thank you very much!

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If I understand correctly, you want a complete match between the rows, where the items need to match exactly.

You have a poor data model. In databases, you should not store repeated values in columns. Instead, you should store them in rows.

However, it is pretty easy to unpivot the data:

with t1 as (
      select t1.*, v.item,
             count(*) over (partition by user_id) as cnt
      from table1 t1 cross apply
           (values (t1.item_1), (t1.item_2), (t1.item_3)
           ) v(item)
      where v.item is not null
     ),
     t2 as (
      select t2.*, v.item,
             count(*) over (partition by role) as cnt
      from table2 t2 cross apply
           (values (t2.item_1), (t2.item_2), (t2.item_3)
           ) v(item)
      where v.item is not null
     )
select t1.user_id, t1.user_name, t2.role
from t1 join
     t2
     on t1.item = t2.item and t1.cnt = t2.cnt
group by t1.user_id, t1.user_name, t2.role, t1.cnt
having count(*) = t1.cnt;

Upvotes: 1

Related Questions