Pasqualz Posted January 15, 2007 Share Posted January 15, 2007 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, gradeJohn, history, 91Sally, math , 75Steve, english, 80Joe, science, 95It'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? Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/ Share on other sites More sharing options...
effigy Posted January 15, 2007 Share Posted January 15, 2007 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." Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161096 Share on other sites More sharing options...
Pasqualz Posted January 15, 2007 Author Share Posted January 15, 2007 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! Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161114 Share on other sites More sharing options...
effigy Posted January 15, 2007 Share Posted January 15, 2007 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. Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161117 Share on other sites More sharing options...
Pasqualz Posted January 15, 2007 Author Share Posted January 15, 2007 effigy, I ran a test and fihured out what you just told me here! I think your information will be helpful! Thank You! Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161127 Share on other sites More sharing options...
Pasqualz Posted January 15, 2007 Author Share Posted January 15, 2007 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??? Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161138 Share on other sites More sharing options...
effigy Posted January 15, 2007 Share Posted January 15, 2007 (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] Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161139 Share on other sites More sharing options...
fenway Posted January 15, 2007 Share Posted January 15, 2007 A UNION may be helpful, too. Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161466 Share on other sites More sharing options...
Pasqualz Posted January 15, 2007 Author Share Posted January 15, 2007 Thanks effigy, I will do some research on the Union function. Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-161552 Share on other sites More sharing options...
fenway Posted January 16, 2007 Share Posted January 16, 2007 UNION statements have the added advantage of utilizing indexes on both smaller queries with ranges, instead of having to use "OR". Link to comment https://forums.phpfreaks.com/topic/34239-how-to-get-exceptions-to-a-query/#findComment-162124 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.