CODEWITHSUNDEEP
sqlsql-serverjoin
Dave Carruthers
Dave Carruthers

Reputation: 11

SQL JOIN match single value in 1 table from a list of values in another

I'm trying to find a way to join 2 tables, but I'm struggling.

I want to join where table2.ID is contained in table1.UsedIn

Table 1

|ID      | Name       | UsedIn
|1234    |Part1       | 3333 2222 |
|4321    |Part2       | 2222 1111 |

Table 2

| ID   | Name |
|3333  |Part3 |
|2222  |Part4 |
|1111  |Part5 |

Output

|ID      | Name | UsedIn    | PartID |Name
|1234    |Part1 | 3333 2222 | 3333   | Part3 |                       
|1234    |Part1 | 3333 2222 | 2222   | Part4 |
|4321    |Part2 | 2222 1111 | 1111   | part5 |
|4321    |Part2 | 2222 1111 | 2222   | Part4 |

Can anyone help?

Upvotes: 1

Views: 1187

Answers (3)

Caius Jard
Caius Jard

Reputation: 74710

Couple of people have said "fix your model" but you might not know how..

Create another table that associates Components (table 1) with Parts (table 2). (You should always call your tables better names than "table N". I picked these because, to me, a Part is made of Components - a TV power supply is a part, with a part number, that you can buy as a whole thing - it has resistors and capacitors etc which are the components):

CREATE TABLE PartComponents
(
  PartID INTEGER FOREIGN KEY REFERENCES Parts(ID),
  ComponentID INTEGER FOREIGN KEY REFERENCES Components(ID)
)

Use a query like Gordon gave you, to populate this table:

insert into partcomponents(PartID, ComponentID)
select s.value as PartID, t.id as ComponentID
from 
 table1 t 
 cross apply string_split(t1.usedin, ' ') s

Now when you want to know what parts have what components it's a simple JOIN, which will be much faster than splitting the string all the time

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use string_split():

select t1.*, t2.name
from table1 t1 cross apply
     string_split(t1.usedin, ' ') s join
     table2 t2
     on t2.id = s.value;

That said, you should fix your data model. Don't store multiple values in a single column!

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

You could use STRING_SPLIT() function as the following:

WITH T1 AS
(
  SELECT 1234 Id,
         'Part1' Name,
          '3333 2222' UsedIn
  UNION ALL
  SELECT 4321,
         'Part2',
          '2222 1111'
),
T2 AS
(
  SELECT 3333 ID,  'Part3' Name
  UNION ALL
  SELECT 2222,  'Part4'
  UNION ALL
  SELECT 1111,  'Part5'
)
SELECT T.Id,
       T.Name,
       T.UsedIn,
       T2.Id ParentId,
       T2.Name
FROM T2 JOIN
(
  SELECT *
  FROM T1 CROSS APPLY STRING_SPLIT(UsedIn, ' ') SS
) T ON Value = T2.Id;

But the right way is to fix our data modal, then all things becomes easy.

Upvotes: 1

Related Questions