joining 2 selects where a value not in Values

Question

Post reply

joining 2 selects where a value not in Values

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
August 12, 2014 at 2:26 pm

#289035

Ok, another question I have 2 queries where I select all accounts with bill code in 'bmonit' example ('12','39','124','1FA')
then I also have a Select where the same account might have additional services, that are not in example ('12','39','124','1FA') and for these accounts I need to just put a 'Y' if stop_date is null.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

If I understand your question correctly you need to do what is referred to as a Left Anti Join Semi Join.

There are a couple ways you can do this:

DECLARE @bmonit table(billcode varchar(10) not null);

DECLARE @table2 table(billcode varchar(10) not null, stop_date date null);

INSERT @bmonit VALUES ('12'),('39'),('124'),('1FA');

INSERT @table2 VALUES ('12','1/1/2009'),('39','1/1/2009'),('124','1/1/2009'),('1FA','1/1/2009'),('AAA',NULL);

-- Option 1: Using EXCEPT

SELECT billcode, 'Y' as xxx FROM @table2

WHERE stop_date IS NULL

EXCEPT

SELECT billcode, 'Y' FROM @bmonit

-- Option 2: Using NOT IN (this is the better option if you are not filtering out duplicates

SELECT billcode, 'Y' AS xxx

FROM @table2 t2

WHERE stop_date IS NULL

AND billcode NOT IN

(

SELECT billcode FROM @bmonit

);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Alan.B (8/12/2014)
If I understand your question correctly you need to do what is referred to as a Left Anti Join Semi Join.

Not by me! Or anyone else, it seems. Try searching Google for "Left Anti Join Semi Join" and you'll get one hit - yours 😀

And very soon, presumably, two hits.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Phil Parkin (8/13/2014)
Alan.B (8/12/2014)
If I understand your question correctly you need to do what is referred to as a Left Anti Join Semi Join.
Not by me! Or anyone else, it seems. Try searching Google for "Left Anti Join Semi Join" and you'll get one hit - yours 😀
And very soon, presumably, two hits.

Picky today Phil! 😛

It's a typo. Alan meant a left anti semi join.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Aha, OK - perhaps I should have considered that, thanks Chris. Sorry Alan.

I have reason to be picky - I'm building a WiX installer, so I'm in XML hell 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel