Help with an WHERE NOT EXISTS subquery please.

[KathyB]

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.Station
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.Station
FROM tblStations
WHERE NOT EXISTS
(SELECT tblStationUser.Station FROM tblStationUser WHERE
tblStationUser.UserName=@varUserName)
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]

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.Station
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" <Ka**********@attbi.com> wrote in message
news:75**************************@posting.google.c om...

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.Station
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.Station
FROM tblStations
WHERE NOT EXISTS
(SELECT tblStationUser.Station FROM tblStationUser WHERE
tblStationUser.UserName=@varUserName)
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

[Kathy Burke]

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!

[Shervin Shapourian]

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

Shervin

"Kathy Burke" <ka**********@comcast.net> wrote in message
news:3f*********************@news.frii.net...

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!