selecting the opposite?

fikse

I have a table of student applications.

studentID
class
status

Students are allowed to apply for more than one class. So each student will have multiple records in the table. The status column will have a value of 'approved', 'declined' or 'awaiting review'.

I need to select all of the students that have not been approved for ANY classes.

I have this query which selects all of the students who have been approved for at least one class:

SELECT DISTINCT * FROM applications WHERE status = 'approved'

Is there a way to select the opposite of the above query? I tried:

SELECT DISTINCT * FROM applications WHERE NOT (status = 'approved')

but that just selects all the records where status doesn't = approved.... which doesn't help me....

can someone suggest a query that will select all of the students that have not been approved for ANY classes?

any help would be great....

thanks...

cgraz

Try

SELECT DISTINCT * FROM applications WHERE status NOT LIKE 'approved'

You can find more info here: http://www.mysql.com/doc/en/String_comparison_functions.html

Cgraz

fikse

Cgraz, thanks for the reply, but the results of your query will not take into account that the student might have been approved for a class... it only selects the classes that they were not approved for...

I need to select all of the students who have not been approved for ANY classes...

thanks....

nnichols

You can't do it in a single query without the use of sub-selects, which mysql does not support YET.

Use something like this -

$sql = "SELECT DISTINCT(studentID) FROM applications WHERE status='approved'";
$res = mysql_query($sql);
while ($row = mysql_fetch_row($res)) {
// build array of studentIDs
$studentIDs[] = $row[0];
}
//build comma separated list of studentIDs
$studentIDlist = implode(', ', $studentIDs);

// now get all students who don't appear in previous query
$sql = "SELECT * FROM applications WHERE studentID NOT IN ($studentIDlist)";
$res = mysql_query($sql);

Hope this helps 😉

P.S. I assume you are using MySQL

fikse

thanks nnichols, my next step was to use PHP, and do two queries.... 🙂

nnichols

You might be able to do it using a crosstab query something like this -

SELECT studentID,
 SUM(IF(status='accepted',1,0)) AS a,
 SUM(IF(status='declined',1,0)) AS d,
 SUM(IF(status='awaiting',1,0)) AS aw
FROM applications 
GROUP BY studentID
HAVING a=0

Have a play with it and see what you can come up with.

Report back if you get it to work with the crosstab

fikse

I played around with your sample query a little and couldn't get it working.... I'm not familar at all with crosstabs.... although I can kind of see what your query was trying to accomplish....

I've got it working with the two query solution for now....

I wonder when mysql will support sub-selects..... I run into these kind of selects often, and usually end up doing selects on top of selects via loops in PHP.....