Do Not Return Matching Rows

Question

Post reply

Do Not Return Matching Rows

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 23, 2016 at 10:17 am

#400612

Hello Everyone
I hope that you all are having a wonderful Database Day.
I ran into a rather odd kind of request.
I have added some bogus code to help visualize what I am trying to perform
DECLARE @Cities TABLE
(
CityName varchar(25)
,StateName varchar(25)
)
INSERT INTO @Cities
SELECT 'DodgeCity', 'Kansas' UNION ALL
SELECT 'DodgeCity', 'Missouri' UNION ALL
SELECT 'DodgeCity', 'All' UNION ALL
SELECT 'Maimi', 'Florida' UNION ALL
SELECT 'Maimi', 'Ohio' UNION ALL
SELECT 'Maimi', 'Washington' UNION ALL
SELECT 'San Diego', 'Texas' UNION ALL
SELECT 'San Diego', 'California' UNION ALL
SELECT 'Austin', 'Texas' UNION ALL
SELECT 'Austin', 'Oregon' UNION ALL
SELECT 'Austin', 'All'
SELECT * FROM @Cities
This is pretty simple. But I want the resultset to return Only the CityNames where a Statename does not have the word "All". So the rows of "Austin" and "DodgeCity" would NOTbe in the resultset.
This is what I want the resultset to be:
CityNameStateName
Maimi Florida
Maimi Ohio
Maimi Washington
San DiegoTexas
San DiegoCalifornia
I have tried what I know, but I am missing something, and I feel it is something simple.
Thank You in advance for your assistance, time and examples
Andrew SQLDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SELECT CityName FROM @Cities where CityName NOT IN (SELECT CityName from @Cities WHERE StateNAme = 'ALL');

Edit: And make sure that CityName is not nullable column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

SELECT *

FROM @Cities c1

WHERE NOT EXISTS ( SELECT c2.CityName

FROM @Cities c2

WHERE c2.StateName = 'All'

AND c2.CityName = c1.CityName );

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

Thank You both for the very fast response and the examples.

I greatly appreciate it.

Andrew SQLDBA

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Pssst... "MIAMI"

Also, I'm missing something.

Why not just select where [Statename] <> 'ALL' ?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The Dixie Flatline (8/23/2016)
Pssst... "MIAMI"
Also, I'm missing something.
Why not just select where [Statename] <> 'ALL' ?

Because he wants the cities where there's no row for that city with the StateName ALL. A <>'ALL' will return Austin and DodgeCity, which shouldn't be returned.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shirolkar.anand Valued Member Points: 59 More actions · Answer 6

you can use the following code to get the desired O/P-

SELECT CityName,StateName FROM @Cities where CityName NOT IN (SELECT distinct CityName from @Cities WHERE StateNAme = 'ALL')

Thanks,

Anand

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

shirolkar.anand (8/24/2016)
you can use the following code to get the desired O/P-
SELECT CityName,StateName FROM @Cities where CityName NOT IN (SELECT distinct CityName from @Cities WHERE StateNAme = 'ALL')

Which, other than the completely unnecessary DISTINCT, is the exact answer I already posted, including capitalisation mistakes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Thanks, Gail. Evidently my thought processes were shutting down last night from caffeine deprivation.

I am reminded of a very old song by a brilliant comedian named Tom Lehr, which you might enjoy

Lobachevsky

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ScottPletcher SSC Guru Points: 101078 More actions · Answer 9

--

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".