Link to home
Start Free TrialLog in
Avatar of tmajor99
tmajor99

asked on

MS SQL Comparing Singular Value Column to Column Containing Multiple Values

How can i compare a single value to a column containing multiple values.  My table A has singular values and my table B could have multiple values so how would i find matches when comparing a single to multiple values?   I have two tables as shown below.  I am trying to find all the values from Table A that did not exist in Table B.


Table A                Table B
GPH Code           GPH Codes

123456              123456,512344

321900               321900

432981               321999,43281

999111               999999

                             839300


In the example above,  value "999111" does not exist in table B.  

Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image


If you're on a version of SQL Server that has the STRING_SPLIT function available, do this:


SELECT ...

FROM [Table A] A

WHERE NOT EXISTS(

    SELECT 1

    FROM [Table B] B

    CROSS APPLY STRING_SPLIT(B.col, ',') SS

    WHERE SS.value = A.col

)


    

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial