Help with an WHERE NOT EXISTS subquery please.

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • KathyB

    #1

    Help with an WHERE NOT EXISTS subquery please.

    Hi, I have 2 tables:

    tblStations StationID
    Station

    tblStationUser RecordID
    UserName
    Station

    I'm trying to come up with a dataset that contains the
    tblStations.Sta tion
    EXCEPT for where that Station exists in tblStationUser where the
    UserName = @varUserName.

    I've tried this but get 0 rows (I should get about 40):

    SELECT tblStations.Sta tion
    FROM tblStations
    WHERE NOT EXISTS
    (SELECT tblStationUser. Station FROM tblStationUser WHERE
    tblStationUser. UserName=@varUs erName)
    ORDER BY Station

    I tried the subquery separately which returns the correct number of
    rows.

    Any clues as to where I'm going wrong?

    Thanks!
    Kathy
  • Shervin Shapourian

    #2
    Re: Help with an WHERE NOT EXISTS subquery please.

    Kathy,

    You forgot to join tables. Try one of these queries. I haven't tested them.

    SELECT Station
    FROM tblStations
    WHERE NOT EXISTS (SELECT *
    FROM tblStationUser
    WHERE tblStationUser. Station = tblStations.Sta tion
    AND tblStationUser. UserName = @varUserName)
    ORDER BY Station

    OR

    SELECT Station
    FROM tblStations
    WHERE Station NOT IN (SELECT Station
    FROM tblStationUser
    WHERE UserName = @varUserName)
    ORDER BY Station

    Good luck,
    Shervin

    "KathyB" <KathyBurke40@a ttbi.com> wrote in message
    news:75e8d381.0 310160945.2b2df 7e4@posting.goo gle.com...[color=blue]
    > Hi, I have 2 tables:
    >
    > tblStations StationID
    > Station
    >
    > tblStationUser RecordID
    > UserName
    > Station
    >
    > I'm trying to come up with a dataset that contains the
    > tblStations.Sta tion
    > EXCEPT for where that Station exists in tblStationUser where the
    > UserName = @varUserName.
    >
    > I've tried this but get 0 rows (I should get about 40):
    >
    > SELECT tblStations.Sta tion
    > FROM tblStations
    > WHERE NOT EXISTS
    > (SELECT tblStationUser. Station FROM tblStationUser WHERE
    > tblStationUser. UserName=@varUs erName)
    > ORDER BY Station
    >
    > I tried the subquery separately which returns the correct number of
    > rows.
    >
    > Any clues as to where I'm going wrong?
    >
    > Thanks!
    > Kathy[/color]


    Comment

    • Kathy Burke

      #3
      Re: Help with an WHERE NOT EXISTS subquery please.

      Thanks again Shervin. I wish I knew I fraction of what you do!


      Thanks, Kat

      *** Sent via Developersdex http://www.developersdex.com ***
      Don't just participate in USENET...get rewarded for it!

      Comment

      • Shervin Shapourian

        #4
        Re: Help with an WHERE NOT EXISTS subquery please.

        You are welcome Kathy, I'm hanging around here to learn more about SQL
        Server. :-)

        Shervin

        "Kathy Burke" <kathyburke40@c omcast.net> wrote in message
        news:3f8fd569$0 $193$75868355@n ews.frii.net...[color=blue]
        > Thanks again Shervin. I wish I knew I fraction of what you do!
        >
        >
        > Thanks, Kat
        >
        > *** Sent via Developersdex http://www.developersdex.com ***
        > Don't just participate in USENET...get rewarded for it![/color]


        Comment

        Working...