Sub Queries

[Donovan]

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.

 

SELECT a.UID
FROM atlas_tl_session_grade_import a
WHERE a.UID NOT
IN (
SELECT 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

 

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

[tapos]

Use bellow:

SELECT a.UID
FROM atlas_tl_session_grade_import a
WHERE a.UID NOT
IN (
SELECT a2.SOMS_KEY
FROM atlas_tl_students a2
JOIN atlas_tl_group_students b ON ( a2.SOMS_KEY = b.SOMS_KEY )
JOIN atlas_tl_groups c ON ( b.Group_ID = c.Group_ID )
)

Hope this will help u.

--

Thanks,

Tapos Pal

http://tapos.wordpress.com

[toplay]

It's basically doing a left join and using IS NULL on the second table's key.

 

From what I understand of your post, you seem to have a SOMS_KEY in the atlas_tl_group_students table that would correspond to the UID in the atlas_tl_session_grade_import table.

 

So, something like this will return all atlas_tl_session_grade_import rows that are not in atlas_tl_group_students:

 

SELECT

              a.*

 FROM

              atlas_tl_session_grade_import a

LEFT JOIN

              atlas_tl_group_students b

    ON

              b.SOMS_KEY = a.UID

WHERE

              b.SOMS_KEY IS NULL

;

 

Anyway, I'm sure you will get the idea with the left join example and using "IS NULL" (not found) on the primary key column of the second table.

 

 

[Donovan]

It's basically doing a left join and using IS NULL on the second table's key.

 

From what I understand of your post, you seem to have a SOMS_KEY in the atlas_tl_group_students table that would correspond to the UID in the atlas_tl_session_grade_import table.

 

So, something like this will return all atlas_tl_session_grade_import rows that are not in atlas_tl_group_students:

 

SELECT

              a.*

  FROM

              atlas_tl_session_grade_import a

LEFT JOIN

              atlas_tl_group_students b

    ON

              b.SOMS_KEY = a.UID

WHERE

              b.SOMS_KEY IS NULL

;

 

Anyway, I'm sure you will get the idea with the left join example and using "IS NULL" (not found) on the primary key column of the second table.

 

 

 

UID and SOMS_KEY are different values.

 

Testing scantons from the university use UID.  They put that on a excel file along with the score for that test.  I save the file as a csv and then import to a table.  The imported table does not contain a SOMS_KEY.  I need to match UID from the import to the student table and then capture the SOMS_KEY which is also in the student_groups table (on a SOMS_KEY, Group_ID pair),  to find if all the students in the imported table have been assigned to groups.

[toplay]

Well, I didn't get that impression from this part:

 

...

WHERE a.UID NOT

IN (

SELECT a.SOMS_KEY

..

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

...

 

I still don't fully understand, so experiment based on what I already gave you. Just left join the tables, and use the "IS NULL" on the right column (which I assume is atlas_tl_group_students.SOMS_KEY).

 

Good luck.