August 23, 2016 at 10:17 am
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
August 23, 2016 at 10:26 am
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
August 23, 2016 at 10:27 am
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
August 23, 2016 at 10:30 am
Thank You both for the very fast response and the examples.
I greatly appreciate it.
Andrew SQLDBA
August 23, 2016 at 9:03 pm
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
August 24, 2016 at 4:17 am
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
August 24, 2016 at 6:26 am
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
August 24, 2016 at 6:34 am
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
August 24, 2016 at 6:50 am
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
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 3:29 pm
--
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".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply