if exists in group 1

[wanderingaround]

hello... my brain is worn out trying to figure this one out...

I have 2 fields (name, answer)

In my select statement I want to return I third column that states 'true' if at least one 'correct' exists (in the answer field).

something like:

Name Answer AtLeastOneCorrect
Andy wrong true
Andy wrong true
Andy correct true
Murray wrong
Murray wrong
Roger correct true
Roger correct true

As you can imagine... my attempt fails:

select name, answer,
case
when answer = 'correct' then 'true'
end
from TestScores

Any tips.. greatly appreciated!!!

 

[bborissov]

SELECT TestScores.*,
       CorrectAnswer.Fld1
FROM TestScores
LEFT JOIN TestScores CorrectAnswer 
     ON TestScores.Name = CorrectAnswer.Name AND
        CorrectAnswer.Answer = 'correct'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

SELECT t.name
     , t.answer
     , CASE WHEN EXISTS
              ( SELECT 1
                  FROM TestScores
                 WHERE name = t.name
                   AND answer = 'correct' )
            THEN 'true'
            ELSE 'false' END AS AtLeastOneCorrect
  FROM TestScores AS t

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[wanderingaround]

Thanks for both solutions....

r937... your solution fits best with my project... many thanks!

 

[bborissov]

...fits best with my project...

If both solution works you should check the speed
The query in Fields area is executed for EVERY record.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.