www.webdeveloper.com
Page 1 of 3 123 LastLast
Results 1 to 15 of 39

Thread: [RESOLVED] LEFT OUTER JOIN help

  1. #1
    Join Date
    Apr 2008
    Posts
    38

    resolved [RESOLVED] LEFT OUTER JOIN help

    Structure dump

    Code:
    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"
    ); 

  2. #2
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    PHP Code:
    $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

    $getgroupmembers $db->sql_query($query); 

    Could you print you query and post it?

    I guess I am just thinking out loud above.

  3. #3
    Join Date
    Apr 2008
    Posts
    38
    Here is the result.
    Code:
    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

  4. #4
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    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 satlas_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

  5. #5
    Join Date
    Apr 2008
    Posts
    38
    Quote Originally Posted by Phill Pafford View Post
    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 satlas_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.

    You want me to just run this in phpmyadmin?

  6. #6
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    sure what ever is easiest for you

  7. #7
    Join Date
    Apr 2008
    Posts
    38
    It didn't like this:

    #1054 - Unknown column 's.SOMS_KEY' in 'on clause'

  8. #8
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    Is the atlas_tl_students table the same structure are this table tl_students

    Code:
    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,  /* <-- Is there */
      `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
    I just removed the LEFT JOIN from your query and added the table in the FROM clause

  9. #9
    Join Date
    Apr 2008
    Posts
    38
    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.

    I've been hammering on this for two days.

  10. #10
    Join Date
    Apr 2008
    Posts
    38
    Quote Originally Posted by Phill Pafford View Post
    Is the atlas_tl_students table the same structure are this table tl_students
    They are the same table.

    It doesn't like it in phpmyadmin. I get no error in the php script. I still get no team members displayed.

    $groupmember_total = $db->sql_numrows($getgroupmembers);
    if($groupmember_total = 0){
    All evaluation are complete.
    }
    Last edited by SFDonovan; 10-16-2008 at 02:14 PM.

  11. #11
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    Try this

    PHP Code:
    SELECT s.*
    FROM atlas_tl_students AS satlas_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 satlas_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 satlas_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 

  12. #12
    Join Date
    Apr 2008
    Posts
    38
    Nothing.

    I'm pretty stumped.

    Here is a dump of my students table.
    Code:
    atlas_tl_students` (`Name_Last`, `Name_First`, `Email`, `LDAP_USER`, `Class_Year`, `SOMS_KEY`, `UID`) VALUES
    ('Donovan', 'Steven', 'steven.donovan@xxx.edu', 'w002sfd', '2', 999, 'U00559693'),
    ('Anderson', 'Mark', 'mark.anderson@xxx.edu', 'w003mda', '2', 548, 'U00583949'),
    ('Needles', 'John', 'john.needles@xxx.edu', 'w001jan', '2', 537, 'U00580537'),
    ('Harbert', 'Darren', 'darren.harbert@xxx.edu', 'w001dxh', '2', 496, 'U00583382'),
    ('Trimboli', 'Sandy', 'sandra.trimboli@xxx.edu', 'w003skt', '2', 539, 'U00583903'),
    ('Merrill', 'Valencia', 'valencia.merrill@xxx.edu', 'w103vlm', '2', 562, 'U00583822'),
    ('Shafeek', 'Ife', 'ife.shafeek@xxx.edu', 'w001ifs', '2', 448, 'U00399476');
    my group students table:

    Code:
    atlas_tl_group_students` (`Group_ID`, `SOMS_KEY`, `LDAP_USER`) VALUES
    (20, 539, 'w003skt'),
    (20, 448, 'w001ifs'),
    (19, 496, 'w001dxh'),
    (19, 999, 'w002sfd'),
    (19, 548, 'w003mda'),
    (20, 537, 'w001jan'),
    (20, 562, 'w103vlm');
    I'm in group 19 with Darren and Mark yet I don't see them. All I get is a zero result when I do sql_numrows

    The EXPLAIN only showed the same error as before.

  13. #13
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    Hmm, Try this in the phpMyAdmin

    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 satlas_tl_M2peereval AS peatlas_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 

  14. #14
    Join Date
    Apr 2008
    Posts
    38
    MySQL returned an empty result set (i.e. zero rows)

  15. #15
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    1,046
    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()); 

    echo 
    $getgroupmembers

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles