Do Not Return Matching Rows

  • 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

  • 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
  • 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

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

    I greatly appreciate it.

    Andrew SQLDBA

  • 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

  • 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
  • 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

  • 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
  • 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

  • --

    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