Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

if exists in group 1

Status
Not open for further replies.
Jan 31, 2010
21
GB
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!!!

 
Code:
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.
 
Code:
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
 
Thanks for both solutions....

r937... your solution fits best with my project... many thanks!
 
...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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top