Join columns within the same table


I have table like this:

The ID1 and ID2 are connected like this:

I want to do a join within the table (ID1 = ID2), to end up with this:

I'm now trying to do it with the Join function in creating a visualization, but I end up with a table that is not right. It does not return all the unique values So somewhere in the joining within the table it goes wrong. Could someone help me?

Hi @lotwijna
It would likely be easier to try to manipulate your data in SQL. It's an uncommon structure.

@flamber Ah thats good to know, thought this might be possible without SQL, I will try it with, see if I can get a bit further

What was your DB designer thinking?!!

Create 2 views:

  1. SELECT ID1, ID2, Title from yuckTable where Filter = 0
  2. SELECT ID2, Genre from yuckTable where Filter =1

Then join them