Link to home
Start Free TrialLog in
Avatar of hojohappy
hojohappy

asked on

Help with SQL Command to compare the contents of two table fields

I need help with SQL command.  I have two SQL server tables with I need to validate the contents of one table field against another.  The table [VG-Attributes].LOVs contains a partial set of values but I need to validate these values against the complete set which is in [VG-LOVs].[Combined Field Type Values].

[VG-Attributes].LOVs = No
[VG-LOVs].[Combined Field Type Values]  = Yes;No

I need to report any value from [VG-Attributes].LOVs that is not in [VG-LOVs].[Combined Field Type Values].  For example;

[VG-Attributes].LOVs = XX
[VG-LOVs].[Combined Field Type Values]  = Yes;No


SELECT     [VG-Attributes].LOVs, [VG-LOVs].[Combined Field Type Values]
FROM         [VG-Attributes] INNER JOIN
                      [VG-LOVs] ON [VG-Attributes].LOV_ID = [VG-LOVs].[LOV ID ]
                   where [VG-Attributes].LOVs does not contain all values from [VG-LOVs].[Combined Field Type Values]
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
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
I would use PATINDEX SQL Function to check existence of one string in the other:


patindex:  http://msdn.microsoft.com/en-us/library/ms188395(SQL.105).aspx

--this will show all that NOT exists and if you want the match just change  = 0 to > 0 in query below

SELECT     [VG-Attributes].LOVs, [VG-LOVs].[Combined Field Type Values]
FROM         [VG-Attributes] INNER JOIN
                      [VG-LOVs] ON [VG-Attributes].LOV_ID = [VG-LOVs].[LOV ID ]
where patindex([VG-Attributes].LOVs,[Combined Field Type Values])=0