SQL SELECT PROBLEM - 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

General SQL Server Forums

New to SQL Server Programming

SQL SELECT PROBLEM

Author

Topic

marzipan
Starting Member

10 Posts

Posted - 2005-11-11 : 16:10:34

hi guys,

this is my first post, and i know i am a beginner but i really need some help.

i have a table in sql 2000.

with the following coloumns

EntryID (primary key)
RightOrWrong (bit) 1 or 0
EntrantsEpost (nvarchar)
CompetitionDay (numeric)
Answer (nvarchar)

bascially the user enters his or her answer each day and if the answer is correct then a 1 would be written, wrong and a 0 would be written.

so for example,

EntryID RightOrWrong EntrantsEpost CompetitionDay Answer
---------------------------------------------------------------------
1 1 bob@as.com 1 A
2 0 jen@as.com 1 B
3 1 bob@as.com 2 C
4 1 sam@as.com 2 C
5 0 jen@as.com 2 B
6 1 bob@as.com 3 A
7 1 sam@as.com 3 B

my problem is that i need to be able to return the results of all users who have got only correct answers for all days.

for example,

SELECT DISTINCT EntrantsEpost
FROM ENTRIES
WHERE (RightOrWrong = 1) AND (CompetitionDay BETWEEN '1' AND '3')

i can't seen to return the results I need I get either all the users, or all the users who have the correct entries on any of the days.

but I need to return the results of the users who have got only correct answers for all days, for example, this would be bob, or using the COUNT, be 1.

can anyone help me please?

thank you. marthen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-11 : 16:28:04

try this:


SELECT EntrantsEpost
FROM ENTRIES
WHERE (RightOrWrong = 1) 
group by EntrantsEpost
having count(*) = (select count(distinct CompetitionDay) from ENTRIES)

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources