Join table A with table B where field in A found in field which is an array in table B

Question

Hi Gurus, Table 1 has a list of activities. Table 2 has a field which is an array with ; as separator of the PK's of table 1. I need to do a join between the two where the PK in table 1 is found in the array of the field in table 2. While joins are not a problem for me, I've never encountered this before & my searches have only turned up reference to mysql & postgre databases queries.

Can this be done at server query level or do I need to bring to the app in datatables & iterate through? If it's possible could you give me a pointer on how I could achieve it. Any guidance much appreciated

Answer 1

Try something like this:

select * from TableA a, TableB b
cross apply string_split(b.ArrayField, ';') s 
where a.PK = s.value

Answer 2

string_split was added in SQL 2016.

This way of storing data is not good, because relational databases are not designed for this. You need to crack the list into table format. This article on my web site discusses this more: http://www.sommarskog.se/arrays-in-sql.html

Answer 3

Hi @ HughNaude-7306,

Welcome to the Microsoft TSQL Q&A Forum!

When you post a new question, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

string_split cannot be used in your version, you can replace it with a custom function.

Please refer to:

--1.Create test data  
CREATE TABLE SourcetableB(ID INT,String VARCHAR(MAX))  
INSERT INTO SourcetableB VALUES(1,'abc,def,pqr,xyz'),(2,'pqr,xyz,ghi,abc')  
--2.Create user-defined functions  
CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))       
RETURNS @result TABLE(F1 VARCHAR(100))       
  AS         
   BEGIN       
   DECLARE @sql AS VARCHAR(100)       
  SET @Sourcestr=@Sourcestr+@Seprate         
  WHILE(@Sourcestr<>'')       
  BEGIN       
    SET @sql=left(@Sourcestr,CHARINDEX(',',@Sourcestr,1)-1)       
    INSERT @result VALUES(@sql)       
     SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(',',@Sourcestr,1),'')       
   END       
   RETURN    
   END  
 GO  
--3.Call functions  
SELECT *   
FROM SourcetableB s   
CROSS APPLY SplitStr(S.string,',') V;  

--Call  custom function with your table  
 select * from TableA a, TableB b  
 cross apply SplitStr(b.ArrayField, ';') s   
 where a.PK = s.value  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.