1

This should be straight forward, but I am getting my tail kicked with the proper syntax.

I have this syntax

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM 
    ___SampleRequired bm
LEFT JOIN 
    ___Sample td 
    ON LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name))
WHERE 
    bm.Type LIKE '%Mandate%'
    AND bm.Location = 'StapleChase'

which I want to show all beer that exist in the SampleRequired Table that DO NOT exist in the Sample Table. When I execute the above query I get 24 results returned. My expected returned result set is 23 because

BLUE MOON BELGIAN WHITE

exists in both of the tables.

How should I change this query so that I ONLY get the results that exist in SampleRequired that DO NOT exist in Sample?

***Had to use pastebin bc SO complained my post was mostly code...sorry

https://pastebin.com/jH6eX2du

  • 1
    You could add to WHERE clause AND td.Beer_name IS NULL – McNets Oct 7 '19 at 13:48
  • That does it...care to explain why? – JamesL Oct 7 '19 at 13:50
2

Using NOT EXISTS

Select DISTINCT
    bm.Location
    ,bm.[Beer (Upcoming Menu)]
FROM 
    ___SampleRequired bm
WHERE NOT EXISTS 
   (SELECT 1 
    FROM ___Sample td 
    WHERE LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name)))
AND bm.Type LIKE '%Mandate%'
AND bm.Location = 'StapleChase'

Also, Max Vernon has a nice answer comparing LEFT JOIN vs NOT EXIST It's also more common to see this in the wild versus the LEFT JOIN method where you also need to add table.key IS NULL in the WHERE clause. i.e td.Beer_name IS NULL

And, Martin Smith comparing NOT IN vs NOT EXIST.

| improve this answer | |
  • This method returns the expected results but does it keep into account my WHERE clause? bm.Type LIKE '%Mandate%' AND bm.Location = 'StapleChase' – JamesL Oct 7 '19 at 13:49
  • Fixed @JamesL i forgot to add that – scsimon Oct 7 '19 at 13:50
0

Although I think scsimon answer is da best, you can get the same result by adding:

AND td.Beer_name IS NULL

LEFT JOIN selects all rows from __SampleRequired table that match WHERE condition, but add nulls for those __Sample rows that do not match ON condition.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.