Help With Simple SQL Query - 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

Transact-SQL (2000)

Help With Simple SQL Query

Author

Topic

gaming_mouse
Starting Member

4 Posts

Posted - 2005-04-14 : 06:53:19

I used to write lots of SQL for an Oracle db when I worked as a web developer. This is a simple query I wrote for an Access db, and it's crashing Access (perhaps b/c of a syntactical idiosyncrasy, or perhaps b/c I just have it wrong). If this is isn't the correct forum, please let me know. I'm trying to count the number of distince player_id's in the table game_players:

SELECT count(*) AS NumPlayers
FROM game_players
WHERE player_id IN (
SELECT DISTINCT player_id
FROM game_players);

TIA,
gm

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-14 : 07:39:55

Looks like it would work in SQL, but DISTINCT isn't required. Maybe Access won't crash if you omit the Distinct?

In SQL (I don't know Access), another form which might work:

SELECT COUNT(DISTINCT player_ID) As NumPlayers FROM game_players

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-14 : 08:50:25

Sam's answer is sound and correct, but to get back to your original SQL statement -- your problem might be that you are confusing:

SELECT * FROM TABLE WHERE IN (some select ...)

with

SELECT * FROM (some select) AS Alias

Two very different beasts !

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-14 : 08:57:59

Ah so Jeff! I missed that.

gm's query doesn't count distinct at all.

Sam

quote:
Originally posted by jsmith8858

Sam's answer is sound and correct

-- It's good to be sound and correct once in a while. Someday I hope to be absolutely correct.

gaming_mouse
Starting Member

4 Posts

Posted - 2005-04-14 : 16:42:59

quote:
Originally posted by jsmith8858

Sam's answer is sound and correct, but to get back to your original SQL statement -- your problem might be that you are confusing:

SELECT * FROM TABLE WHERE IN (some select ...)

with

SELECT * FROM (some select) AS Alias

Two very different beasts !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources