tl_students`
`Name_Last` text,
`Name_First` text,
`Email` varchar(30) default NULL,
`LDAP_USER` varchar(255) NOT NULL default '',
`Class_Year` varchar(6) default NULL,
`SOMS_KEY` int(11) default NULL,
`UID` varchar(9) default NULL,
tl_group_students`
`Group_ID` int(11) NOT NULL default '0',
`SOMS_KEY` int(11) NOT NULL default '0',
`LDAP_USER` varchar(255) NOT NULL default ''
tl_M2peereval`
`UID` varchar(9) NOT NULL,
`rater` varchar(10) NOT NULL,
`qa_1_rating` tinyint(1) unsigned NOT NULL,
`qa_2_rating` tinyint(1) unsigned NOT NULL,
`datesubmit` date NOT NULL,
`Academic_Year` varchar(5) NOT NULL,
`term` varchar(10) NOT NULL
It's a peer evaluation and I need to find all students in my group that I "$authuser" have not yet done an evaluation on.
$authuser is my LDAP_USER value from the student table.
I need to see everybody in my team but I am having an error.
I have three teammates in my group. My teammate Mark has already filled out his evaluation on Darren. Now I can no longer see Darren in the list even though I have not yet filled out the eval.
$UserGroup = User group for my team from the tl_group_students table.
rater in the tl_M2peereval is $authuser or who filled out the evaluation.
Same value for LDAP_USER in student table. Like I said I captured the LDAP_USER and assigned it to $authuser during authentication.
My query is close but I'm overlooking something. I only get one of my team mates (Mark) to show up and he is one who has already completed the evaluations on both Darren and myself.
This tells me that Darren's UID exist in the table, but I am not the rater for it... Mark is.
I hope I'm making sense.
Here is my query.
PHP Code:
$getgroupmembers = $db->sql_query("SELECT s.* FROM ".$prefix."_tl_students s
LEFT JOIN ".$prefix."_tl_M2peereval pe ON (s.UID = pe.UID)
JOIN ".$prefix."_tl_group_students gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND s.LDAP_USER != '$authuser'
AND pe.UID IS NULL");
$query = "SELECT s.* FROM ".$prefix."_tl_students AS s /* What data do you need returned? you are returning all of the students fields is this wanted? */
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (s.UID = pe.UID) /* Looks to limit */
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY) /* Looks to limit to the student groups */
WHERE gs.Group_ID = '$UserGroup' /* limts to one group */
AND s.LDAP_USER != '$authuser' /* Return everything but the authuser? */
AND pe.UID IS NULL"; /* Limits to peers not reviewed */
echo $query . "<br />\n"; //<-- Print query to screen
SELECT s.* FROM atlas_tl_students AS s /* What data do you need returned? you are returning all of the students fields is this wanted? */ LEFT JOIN atlas_tl_M2peereval AS pe ON (s.UID = pe.UID) /* Looks to limit */ JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY) /* Looks to limit to the student groups */ WHERE gs.Group_ID = '19' /* limts to one group */ AND s.LDAP_USER != 'w002sfd' /* Return everything but the authuser? */ AND pe.UID IS NULL
Ok when a user does the peer review does his pe.UID get entered into the atlas_tl_M2peereval table?
I think the LEFT JOIN is not needed
PHP Code:
SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '19' /* This is limiting to the group */
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL /* This condition is limiting the UID's that have no value */
Ok when a user does the peer review does his pe.UID get entered into the atlas_tl_M2peereval table?
I think the LEFT JOIN is not needed
PHP Code:
SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '19' /* This is limiting to the group */
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL /* This condition is limiting the UID's that have no value */
Try it and let me know
Yes the UID is entered. It should end up as the rater does his eval's on all his team members. Each having their own UID and his rater written to the record. I want to find all team members who I have not yet done an evaluation for.
One thing I was having trouble with was my other team members would submit their evals on the 4 or 5 in our team and the UID would then exist in the table. I pulled up the page and had missing team members even though I had never submitted and eval and my rater was written to any records.
Example:
Steve logs in and submits evaluations on Mark and Darren, but not Judy and Bob.
Bob logs in and cannot see Mark or Darren but does see Judy's name to select and fill out an evaluation.
SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (s.SOMS_KEY = gs.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL
or
PHP Code:
SELECT
s.Name_Last,
s.Name_First,
s.Email,
s.LDAP_USER,
s.Class_Year,
s.SOMS_KEY,
s.UID
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (s.SOMS_KEY = gs.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL
Try in PHPMyAdmin
Also you might want to use the EXPLAIN command like this to see whats throwing the error in MySQL.
PHP Code:
EXPLAIN SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (s.SOMS_KEY = gs.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL
SELECT
s.Name_Last,
s.Name_First,
s.Email,
s.LDAP_USER,
s.Class_Year,
s.SOMS_KEY,
s.UID
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe, atlas_tl_group_students AS gs
WHERE gs.Group_ID = '19'
AND s.SOMS_KEY = gs.SOMS_KEY
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL
Well looks like the query you started off with is throwing an error and you might not be catching it. I would break down the query into steps like. First I need to find this in table A, then I need to find this from table B and so on.
Maybe add this or die(mysql_error());
PHP Code:
$getgroupmembers = $db->sql_query("SELECT s.* FROM ".$prefix."_tl_students s
LEFT JOIN ".$prefix."_tl_M2peereval pe ON (s.UID = pe.UID)
JOIN ".$prefix."_tl_group_students gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND s.LDAP_USER != '$authuser'
AND pe.UID IS NULL") or die(mysql_error());
[RESOLVED] LEFT OUTER JOIN help
Reply With Quote
Originally Posted by Phill Pafford
Bookmarks