Sub Queries

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I need to find if any students in my imported table "atlas_tl_session_grade_import" do not yet belong to a group in the "atlas_tl_group_students"

The import table contains the unique field UID
The student table has both SOMS_KEY and UID.
The student group table contain the unique field SOMS_KEY and Group_ID
The group table has Group_ID

I am attempting a sub query to do this but don't know if I'm on the right track.

Code:

SELECT a.UID FROM atlas_tl_session_grade_import a


WHERE a.UID NOT IN (SELECT a.UID a.SOMS_KEY FROM atlas_tl_students a  


JOIN atlas_tl_group_students b ON (a.SOMS_KEY = b.SOMS_KEY) 


JOIN atlas_tl_groups c ON (b.Group_ID = c.Group_ID))

I then will do a sql_numrows

then

Code:




if ( $group_total > 0 ) {


OpenTable();


echo"<tr><td>There are student grades from this import who have not yet been assigned to a group.</td></tr>";


echo"<tr><td>Please ensure all students for this class year have been assigned to their TL group.</td></tr>";


echo"<tr><td><input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\"></td></tr>";


Closetable();


die(); 


} else {

ect

Posted: Wed Jan 16, 2008 4:36 pm

Not 100% sure on the whole SQL, but the concept, I think, is sound. However, you can only return a.UID in your subselect since that is what you are comparing too (i believe) and you might also want to do a select distinct to remove dups.

Posted: Thu Jan 17, 2008 7:30 am

Thanks

Posted: Thu Jan 17, 2008 1:58 pm

A simple left join should do what you are looking for, then you can just do a count of the results to see if it is greater than 0
I used the main user table and the journal table as an example for you here.

Code:




SELECT nuke_users.user_id, nuke_users.name, nuke_users.username, nuke_users.user_email


FROM nuke_users LEFT JOIN nuke_journal ON nuke_users.username = nuke_journal.aid


WHERE (((nuke_journal.aid) Is Null))