iif question

[laailalalaa]

i have table Students(Name, Grade). i want to display records like
[studentname, "yes"] if a student has only grades > 8. i tried to accomplish this using iif function like this

select Student, iif(Grade>8, "yes", "no") As Expr
from Students
group by Student, Expr

which obviously doesn't work.

any ideas on how to proceed?

thanks

 

[gemma-the-husky]

do it the other way

find students with any grade less than 8 - and then find all the others with an unmatched query

(its also the way to work out a lot of probabilty things - find the chance of something not happening, and take it away from 1, to find the chance of it happening)

 

[georgedwilkinson]

Please consider the meaning of the word "obvious". It is not obvious to us.

How do you know it doesn't work? Do you get an error? What is it? Does it display the wrong data? What? No data? What do you expect to see that you don't? What do/does the table(s) look like?

I would never expect to see a "grade" field in a "student" table...doesn't make sense from a normalization perspective. That could be your problem. Another problem could be your use of "group by". Why are you using "group by" this way? What happens if you leave the "group by" off? It doesn't "look" right but there's no way for us, who are nowhere near your database, to make that call, since we cannot see your tables.

Consider these things. If that doesn't get you closer to the solution, please provide us with information.

 

[laailalalaa]

thanks .. that did the trick
i haven't given any thought to this solution 'cause i wanted to write less code using iif.

do it the other way

find students with any grade less than 8 - and then find all the others with an unmatched query

(its also the way to work out a lot of probabilty things - find the chance of something not happening, and take it away from 1, to find the chance of it happening)

 

[laailalalaa]

well my question was intented for people (better at writing sql queries than me) that can tell the result of that query is obiously wrong.

however if maria had had a 7 and a 9, that query would've returned [maria, "yes"] and [maria, "no"] (2 records, instead of the desired [maria, "no"] record).

of course the db 'doesn't make sense from a normalization perspective', since it's (so) obviously not normalised. but that's how i need it (otherwise i would've obviously changed the design).

i used group by cause i tried to somehow group students by name and use iif to give a "yes" if a student has only grades > 8. so obviously wrong.

thanks for your help though

Please consider the meaning of the word "obvious". It is not obvious to us.

How do you know it doesn't work? Do you get an error? What is it? Does it display the wrong data? What? No data? What do you expect to see that you don't? What do/does the table(s) look like?

I would never expect to see a "grade" field in a "student" table...doesn't make sense from a normalization perspective. That could be your problem. Another problem could be your use of "group by". Why are you using "group by" this way? What happens if you leave the "group by" off? It doesn't "look" right but there's no way for us, who are nowhere near your database, to make that call, since we cannot see your tables.

Consider these things. If that doesn't get you closer to the solution, please provide us with information.