need help joining 4 tables in mysql

[adavis]

I am working on a homework project that has 4 tables. The student logs in with first/last name and grade, and from there, I need to retrieve their homework assignments. My tables are as follows:

HS_coursetable
    Field       Type
    id          int(10)
    courseid    varchar(15)
    sectionid   varchar(10)
    period      tinyint(4)
    room        varchar(6)
    staffid     int(11)
    coursename  varchar(50)
    subject     varchar(50)
    grade       varchar(15)

HS_homework
    Field       Type
    assignid    int(11)
    courseid    varchar(20)
    S01A        varchar(4)
    S02A        varchar(4)
    S03A        varchar(4)
    S04A        varchar(4)
    S05A        varchar(4)
    S06A        varchar(4)
    S07A        varchar(4)
    S08A        varchar(4)
    Monday      date
    assignMon   text
    assignTue   text
    assignWed   text
    assignThu   text
    assignFri   text
    staffid     int(11)

HS_scheduletable
    Field       Type
    id          int(11)
    courseid    varchar(30)
    sectionid   varchar(10)
    studentid   nt(11)

HS_studenttable
    Field       Type
    studentid   int(10)
    lastname    varchar(20)
    firstname   varchar(20)
    grade       varchar(5)	

In there scheduletable, there are 8 entries for each student, so that's the number of rows I need to get back. I need the student's name, grade, class period, course name, subject, week start date (Monday), and the assignments for Mon-Fri.

This is the select statment I tried, but get no rows back:

SELECT HS_studenttable.lastname,
       HS_studenttable.firstname,
       HS_studenttable.grade,
       HS_coursetable.period,
       HS_coursetable.coursename,
       HS_coursetable.subject,
       HS_homework.Monday,
       HS_homework.assignMon,
       HS_homework.assignTue,
       HS_homework.assignWed,
       HS_homework.assignThu,
       HS_homework.assignFri
  FROM HS_studenttable, 
       HS_scheduletable,
       HS_coursetable,
       HS_homework 
 WHERE HS_studenttable.lastname = 'Doe' 
   AND HS_studenttable.firstname = 'John' 
   AND HS_studenttable.grade = '9' 
   AND HS_studenttable.studentid = HS_scheduletable.studentid
   AND HS_scheduletable.courseid = HS_coursetable.courseid
   AND HS_scheduletable.sectionid = HS_coursetable.sectionid
   AND HS_coursetable.courseid = HS_homework.courseid 
   AND (HS_coursetable.sectionid = HS_homework.S01A 
     OR HS_coursetable.sectionid = HS_homework.S02A 
     OR HS_coursetable.sectionid = HS_homework.S03A 
     OR HS_coursetable.sectionid = HS_homework.S04A 
     OR HS_coursetable.sectionid = HS_homework.S05A 
     OR HS_coursetable.sectionid = HS_homework.S06A 
     OR HS_coursetable.sectionid = HS_homework.S07A 
     OR HS_coursetable.sectionid = HS_homework.S08A) 
   AND HS_coursetable.staffid = HS_homework.staffid
ORDER BY HS_coursetable.period

Can anyone give me any advice on this?

[adavis]

Before I knew about joins, I did this in multiple loops, something like this:

$sql1  = "SELECT * FROM studenttable ";
$sql1 .= "WHERE lastname = '$last' ";
$sql1 .= "AND firstname = '$first' ";
$sql1 .= "AND grade = '$grade'";
$result1 = mysql_query($sql1,$dblink) or die("error:".$sql1."\n");
$match1 = 0;
while ($row1 = mysql_fetch_array($result1)) {
  $studentid = $row1['studentid'];
  $match1++;
}
$sql2 = "SELECT * FROM scheduletable WHERE studentid = '$studentid'";
$result2 = mysql_query(sql,$dblink) or die("error:".$sql2."\n");
while ($row2 = mysql_fetch_array($result2)) {
  $courseSQL = $row2['courseid'];
  $sectionSQL = $row2['sectionid'];
  $sql3  = "SELECT * FROM coursetable ";
  $sql3 .= "WHERE courseid = '$courseSQL' AND sectionid = '$sectionSQL' ";
  $sql3 .= "ORDER BY period";
  $result3 = mysql_query($query3,$dblink)  or die("error:".$sql3."\n");
  while ($row3 = mysql_fetch_array($result3)) {
    $sql4  = "SELECT * FROM homework ";
    $sql4 .= "WHERE Monday = '$dateIN' AND courseid = '$courseSQL'";
    if ($sectionSQL == "01A") { $sql4 .= " AND S01A = '$sectionSQL'"; }
    elseif ($sectionSQL == "02A") { $sql4 .= " AND S02A = '$sectionSQL'"; }
    elseif ($sectionSQL == "03A") { $sql4 .= " AND S03A = '$sectionSQL'"; }
    elseif ($sectionSQL == "04A") { $sql4 .= " AND S04A = '$sectionSQL'"; }
    elseif ($sectionSQL == "05A") { $sql4 .= " AND S05A = '$sectionSQL'"; }
    elseif ($sectionSQL == "06A") { $sql4 .= " AND S06A = '$sectionSQL'"; }
    elseif ($sectionSQL == "07A") { $sql4 .= " AND S07A = '$sectionSQL'"; }
    elseif ($sectionSQL == "08A") { $sql4 .= " AND S08A = '$sectionSQL'"; }
    $sql4 .= " ORDER BY Monday LIMIT 5";
    $result4 = mysql_query($query4,$dblink)   or die("error:".$sql4."\n");
    $match4 = 0;
    while ($row4 = mysql_fetch_array($result4)) {
        ... build output
    }
  }
}

Do I need to go back to this approach and stop wasting my time trying to figure out this join? Please don't critique my syntax, this is just pulled from the original to give you an idea. It would have to be changed a bit anyway since I normaized my homework table from one date/one assignment per row to one date (Monday) and 5 assignments per row.

[tomhath]

Are you sure the staffid columns match? That's in your join but not the original.

[adavis]

Ahhhh... Well, when I was working on it this afternoon, I decided that I really didn't need to match on staffid anyway.

I did a quasi join. I read the student table, grabbed the studentid, then did a join on the schedule table and the course table, then from that info, read the homework table. It isn't pretty, but it works. If I get a chance to work on it anymore this weekend, I try leaving out the staffid and see if that makes a difference. It sure would be nice to only have to have one sql statement instead of the 3 I have right now with an imbedded while loop.

[adavis]

I changed the sql statement to:

SELECT st.studentid,
       cr.period, 
       cr.coursename, 
       cr.subject, 
       hw.assignMon, 
       hw.assignTue, 
       hw.assignWed, 
       hw.assignThu, 
       hw.assignFri 
  FROM HS_studenttable as st,  

       HS_scheduletable as sc, 
       HS_coursetable as cr, 
       HS_homework as hw 
 WHERE st.lastname = 'Doe'  

   AND st.firstname = 'John'  

   AND st.grade = '9'  

   AND st.studentid = sc.studentid 
   AND sc.courseid = cr.courseid 
   AND sc.sectionid = cr.sectionid 
   AND cr.courseid = hw.courseid  

   AND (cr.sectionid = hw.S01A  

     OR cr.sectionid = hw.S02A  

     OR cr.sectionid = hw.S03A  

     OR cr.sectionid = hw.S04A  

     OR cr.sectionid = hw.S05A  

     OR cr.sectionid = hw.S06A  

     OR cr.sectionid = hw.S07A  

     OR cr.sectionid = hw.S08A) 
ORDER BY cr.period

and got this error message:

Got error 28 from table handler

I know there's plenty of disk space, so this must be something with the temporary space used to build the response.

I'm running this through phpMyAdmin to try to get the sql statement right.

[tomhath]

There's a problem with the way you are joining Course to Homework. It will pull every student's homework for every section of that course. Might be other problems too. But from your table design, I don't see how you can tell what student is connected to what homework.

[adavis]

From the log in form, I get first/last name, grade, and date (always a Monday). I read the student table to get the student id. I use the student id to read the schedule table and get the courseid and section id. There will be exactly 8 entries per student in the schedule table. Using the courseid and section id, I read the course table to get the period, coursename, and subject AND using the courseid, sectionid, and date I read the homework table to get the Monday-Friday assignments. Since many teachers teach 2-4 sections of the same class, their homework assignments for those classes are usually the same. That is why I have the S01A-S08A, those fields contain the section number. So if I have a sectionid of 01A, I know that I need to match that to column S01A of the homework table, and so on...

I have this working, but have 4 seperate SELECT statements and while loops three deep. I was hoping to uncomplicated it some by using table joins. After much study, I don't think that trying to do all of this in one SELECT statement is possible. I think that I might be able to do 2 SELECT statement, one with student table and schedule table, and the other with course table and homework table. I'm still "studying" on it, but not consumed with getting it working since I have so many other things to do on this site and I have to set my priorities accordingly. In other words, since it is working, if it ain't broke, don't fix it... 😉 Thanks...