How to get exceptions to a query?

[Pasqualz]

Hi All,

I recently posted a question here [url=http://www.phpfreaks.com/forums/index.php/topic,122311.0.html]http://www.phpfreaks.com/forums/index.php/topic,122311.0.html[/url] and I now realize that it brings up an interesting question. How do you get the exceptions to a query? In other words, if you have a simple table:

Name, class, grade
John, history, 91
Sally, math , 75
Steve, english, 80
Joe, science, 95

It's easy to query once and get all the rows where grade > 90, and it's eaqually easy to query a second time and get all the rows where grade < 90, but is it possible to instead, query all rows where grade > 90, then tell the query to give you the exceptions, which would produce the results of the second query. Just curious?

[effigy]

Create a field to indicate this:

[code]SELECT name, class, grade, IF(grade >= 90, 1, 0) AS 90plus FROM table[/code]

[b]Update:[/b] Oops. Changed "id" to "grade."

[Pasqualz]

effigy, thanks for the sample query. I will have to do some research to learn what your query does so that I can try it on my real tables. Can anyone enlightnen me on what the IF(id >= 90, 1, 0) part does, and what the AS qualifier does? Thanks!

[effigy]

I updated my previous post; "id" was left over from my copy/paste.

See IF in [url=http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html]Control Flow Functions[/url]. AS simply creates an alias--without it your column name would literally be "IF(grade >= 90, 1, 0)" instead of a cleaner, clearer "90plus."

The query selects all the records, adding a new column to the ouput called "90plus." If a record's grade is greater than or equal to 90, it will be 1; if not, it will be 0.

[Pasqualz]

effigy, I ran a test and fihured out what you just  told me here! I think your information will be helpful! Thank You!

[Pasqualz]

Effigy, thanks so much. I had no idea you could put conditional statements into a query! I have one other question, the query as structured, returns every row with the aliased '90plus' column. Is there a way to get the query to return [u]only[/u] rows where the aliased '90plus' column =1???

[effigy]

(My)SQL has a lot of capabilities: keep reading the docs and the forums :)

If you still want to work with both result sets, use PHP to separate them: create a loop which only echos the row if 90plus is equal to 1, for instance. Otherwise, if you only want to work with one set of data, you're back where you were at square one:

[quote author=Pasqualz link=topic=122460.msg505097#msg505097 date=1168861889]
It's easy to query once and get all the rows where grade > 90, and it's eaqually easy to query a second time and get all the rows where grade < 90
[/quote]

[fenway]

A UNION may be helpful, too.

[Pasqualz]

Thanks effigy, I will do some research on the Union function.

[fenway]

UNION statements have the added advantage of utilizing indexes on both smaller queries with ranges, instead of having to use "OR".