SQL Return datas from the second table based on the data of the first table

Question

I have two SQL Server tables that are not linked together (No join) and I want to get the data from the second table based on the data of the first table. In the first table I have this:

Table 1

id name ---------- 4 BOX-A 8 PART-D

Table 2

id name ------------ 14 BOX-A1 25 BOX-A2 38 TOOL-A1 39 TOOL-A2 40 PART-D1 41 PART-D2

What I want to do is that for each name found in table 1, I want to return all the matches in the table 2, so at the end I will have something like this:

id name ----------- 14 BOX-A1 25 BOX-A2 40 PART-D1 41 PART-D2

If you are dealing with non-linked DBs, please look into using OPENDATASOURCE to connect to non-linked databases. This will allow you to pass in connection string information to access the second database/table. — Ethilium
– Ethilium, Commented Feb 13, 2017 at 18:15

Gordon Linoff · Accepted Answer · 2017-02-14 02:39:50Z

2

You can use join or exists:

select t2.* from table2 t2 where exists (select 1 from table1 t1 where t2.name like concat(t1.name, '%'));

edited Feb 14, 2017 at 2:39

answered Feb 13, 2017 at 17:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kivo Over a year ago

Excellent ! I just want to add a fix : it's concat(t1.name, '%') and not concat(t2.name, '%') :)

Collectives™ on Stack Overflow

SQL Return datas from the second table based on the data of the first table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related