SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    USA
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with multiple Joins!

    So I have 3 tables and a simplified version of it is as described below:

    Table 1 (Student)
    student_id
    student_name
    school_id (foreigner key for table 2)

    Table 2 (School)
    school_id
    school_name

    Table 3 (Teachers)
    teacher_id
    school_id (foreigner key for table 2)
    teacher_name
    teacher_sequence (1, 2, 3, 4, etc)

    I need to select the student_name, school_name, teacher_name, teacher_name2; where teacher_name is a record in table 3 where teacher_sequence=1 and teacher_name2 is a second record in table 3 where teacher_sequence=2.

    I was able to accomplish this task with the following SQL query:

    select student.student_name, school.school_name, teacher.teacher_name, teacher2.teacher_name from student, school LEFT JOIN teacher ON school .school _id=teacher.school_id, school as school2 LEFT JOIN teacher as teacher2 ON school2.school_id=teacher2.school_id where student.school_id=school.school_id and school.school_id=teacher.school_id and school.school_id=teacher2.school_id and student_id='100' and teacher.teacher_sequence=1 and teacher2.teacher_sequence=2;


    The problem that I am having is when a school only has one teacher I get an empty query! I need to return a student name and school name when the school has one or none teachers!

    I hope I was clear.
    Thanks in advance,

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    is this a homework assignment?

    because it is seriously messed up

    the question, i mean, not your attempted solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    USA
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately is a real case scenario that I've stumbled into. Bad DB design I guess.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you can redo the tables, might this help? (not sure the point of it though). surely the teachers need to be related to students?

    Table 1 (school)
    id
    school_name


    Table 2 (people)
    id
    name
    school_id (foreigner key for table 2)
    role (Teacher or student)
    teacher_sequence (1, 2, 3, 4, etc)

    then

    Code MySQL:
    SELECT
         p1.name
          s.school_name
         p2.teacher_name AS teach1
         p3.teacher_name AS teach2
       FROM people AS p1
    INNER 
       JOIN school AS s
         ON s.id = p1.school_id
    INNER 
       JOIN people AS p2
         ON p2.school_id = p1.school_id
        AND p2.role = 'Teacher'
        AND p2.teacher_sequence = 1
    LEFT OUTER
       JOIN people AS p3
         ON p3.school_id = p1.school_id
        AND p3.role = 'Teacher'
        AND p3.teacher_sequence = 2
    INNER 
       JOIN school AS s
          on s.id = p1.school_id
    WHERE p1.id = 100
    Last edited by IBazz; Jul 8, 2011 at 19:39. Reason: query fix


Tags for this Thread

Bookmarks

Posting Permissions

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