Here's a good one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Here's a good one

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2001-08-01 : 05:48:57

Why does this work....

SELECT DISTINCT COUNT(HostRoom.HostID) 'Count'
FROM HostRoom
INNER JOIN Host_Secondary ON HostRoom.UserID = Host_Secondary.UserID
INNER JOIN HostFeatureProfile ON Host_Secondary.AccountID = HostFeatureProfile.AccountID
AND Host_Secondary.FeatureProfile = HostFeatureProfile.FeatureProfile
WHERE
HostRoom.OnlineNow = 1 AND
Host_Secondary.FeatureProfile IN
(
SELECT HostFeatureProfile.FeatureProfile
FROM HostFeatureProfile
WHERE HostFeatureProfile.AccountID = Host_Secondary.AccountID
)

BUT, this does not?

DECLARE @theCount varchar(2)
SET @theCount =
(
SELECT DISTINCT COUNT(HostRoom.HostID) 'Count'
FROM HostRoom
INNER JOIN Host_Secondary ON HostRoom.UserID = Host_Secondary.UserID
INNER JOIN HostFeatureProfile ON Host_Secondary.AccountID = HostFeatureProfile.AccountID
AND Host_Secondary.FeatureProfile = HostFeatureProfile.FeatureProfile
WHERE
HostRoom.OnlineNow = 1 AND
Host_Secondary.FeatureProfile IN
(
SELECT HostFeatureProfile.FeatureProfile
FROM HostFeatureProfile
WHERE HostFeatureProfile.AccountID = Host_Secondary.AccountID
)
)

Obviously something to do with SETting a value for the variable, but I'm clueless as to why it wouldn't work. It's the exact same query.

Error is:

Server: Msg 107, Level 16, State 3, Line 3
The column prefix 'Host_Secondary' does not match with a table name or alias name used in the query.

-Thanx

Edited by - kelleyb on 08/01/2001 05:51:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources