How to create custom table from two different tables

Question

I am using SQL Server 2008 R2. I have some issues regarding fetching data from two tables.

I have one (first)table showing below:

In this table, I have stored a set of id for the second table.

This here is the second table:

Above image is the master table which has primary keys.

Now I want to create custom table that read the data from first table and check that, that id is exist in second table or not if yes than my custom table should have entry regarding it. I also want to get other records from both tables as well in my custom table.

Any solutions would be appreciated.

Answer 1

Here we will make them using ID values using PARSE NAME and then compare them with both tables for Existing Records

declare @table1 table (ID varchar(10))
insert into @table1(ID) values ('3,4,6'),('1,2,3'),('2,3'),('1,2,3'),('1,2')

declare @table2 table (ID int)
insert into @table2(ID) values(1),(2),(3),(4),(5),(6),(7),(8)

Declare @Custom Table (ID INT)
;WITH CTE AS (

SELECT 
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'ID' 
FROM  
(
     SELECT 
     CAST ('<M>' + REPLACE([ID], ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM @table1     
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a))

INSERT INTO @Custom (ID)
Select T.ID from @table2 T
 WHERE   EXISTS 
 (SELECT DISTINCT C.ID 
        FROM CTE C 
    WHERE T.ID = C.ID) 

select * from @Custom

Answer 2

As per my requirement I got this solution on the base of provided answer by mohan111

;WITH CTE AS (
SELECT 
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'ID'
FROM  
(
     SELECT 
     CAST ('<M>' + REPLACE(Id, ',', '</M><M>') + '</M>' AS XML) AS Data
     FROM table1
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a))

SELECT ColumnName FROM CTE C 
join table2 im on im.Id = c.ID