SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inner Join Query

    Hi all,

    How do I get the instructor values from the below query?
    If the instructor id is different in two rows then only one result overall will be returned.

    How can I fix this?
    PHP Code:
     $query "SELECT * ".
                    
    "FROM class AS cl ".
                      
    "INNER JOIN ".
                      
    "course AS c ".
                      
    "ON cl.course_ID = c.course_ID ".
                      
    "INNER JOIN ".
                      
    "instructor AS i ".
                      
    "ON cl.instructor_ID = i.instructor_ID ".
                      
    "WHERE cl.course_ID = '$course' ".
                      
    "ORDER BY cl.class_ID ".
                      
    "DESC"

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your query is fine

    since you are using "select star" (which is not best practice, by the way), you will automatically get every column from all tables in the join

    if the instructor id is different in two rows and only one result overall is returned, then your script is doing something weird

    run the query outside of php to see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,

    If I use LEFT JOIN for instructor instead of INNER OIN it works fine.

    Why is this?

    thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    possibly because some classes don't have an instructor

    can't say more without seeing your data

    did you run your query outside of php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [UPDATE]
    Realised $course was there. Left it out and got..
    Showing rows 0 - 8 (9 total, Query took 0.0010 sec)
    [/UPDATE]

    Ran the query in mysql and gave this result..
    MySQL returned an empty result set (i.e. zero rows).

    Why is this?

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you have some rows of data that we can look at? It will give a clearer picture for us to help you.


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
  •