Sub Queries

Donovan · Client Joined: Oct 07, 2003 Posts: 691 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

montego · 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.

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

Thanks

mars · 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))