Creating a relationship between several columns

[hudg16]

Hello,
I am trying to attach several columns in one table to one column in another.

Here on the "Accessory" table, we can have up to 3 devices, in the three columns.

Device Type	Device Type2	Device Type3
IPHONE 6S+	IPHONE 7+	IPHONE 8+
IPHONE 12	IPHONE 12PRO
IPHONE 12	IPHONE 12PRO
IPHONE 12	IPHONE 12PRO
IPHONE 12PRMX

On the device table, I only have one device.

Device Out

IPHONE 12

IPHONE 12PRMX

IPHONE 12PRO

I want to it to be so that, if you select an iphone 12 in the device table is linked to an iphone 12 in the accessory table. Or an IPHONE 12PRO to IPHONE12PRO. My previous solution to this was transposing the devices, but this duplicated, and tripled the quantity of accessories I had (since each row is an accessory, with its associated device), with a single row of Device Type in the accessory table.

I understand I should be using some form of merged column for the accessory table, but I am lost on how I can then create this relationship to the device table properly.

 

[severynm]

I would start by unpivoting your Accessory table to two columns: Device Type (1 2 or 3) and name of device. You will then be able to create the relationship between single columns.

 

[hudg16]

I would start by unpivoting your Accessory table to two columns: Device Type (1 2 or 3) and name of device. You will then be able to create the relationship between single columns.

Would this 1) also not double and triple the accessory in that given row, and 2) create issues since you can only have one active relationship at a time? I'm a bit dense on these things.

 

[hudg16]

Would this 1) also not double and triple the accessory in that given row, and 2) create issues since you can only have one active relationship at a time? I'm a bit dense on these things.

To give context the accessory table comes from a larger -- OTTERBOX CASE IPHONE 6+/7+/8+, QUANTITY, SALES, etc. The device names are just parsed from that but still need to remain attached to the quantity and other values.