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.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!
KathyKathy,

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" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310160945.2b2df7e4@.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|||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!|||You are welcome Kathy, I'm hanging around here to learn more about SQL
Server. :-)

Shervin

"Kathy Burke" <kathyburke40@.comcast.net> wrote in message
news:3f8fd569$0$193$75868355@.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!