user3110224 user3110224 - 7 months ago 8
SQL Question

Find unmatched data in SQL

I have this 2 simple tables
enter image description here

I want to select unmatching data from SAMPLE1 by comparing FruitName in SAMPLE2
So far I have tried

SELECT * FROM SAMPLE1,SAMPLE2 WHERE SAMPLE1.FruitName NOT LIKE '%' + dbo.SAMPLE2.FruitName +'%'


But this gives me total 7 records
enter image description here
What I want the output is
enter image description here

Answer
SELECT *
FROM SAMPLE1 s1
WHERE NOT EXISTS (
        SELECT NULL
        FROM SAMPLE2 s2
        WHERE s1.FruitName LIKE '%' + s2.FruitName + '%'
        )