Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Displaying All rows

    I have two tables, student and classes. In the classes table I have classes that students don't belong to. I want to display all of my classes in a query and the students who belong to those classes. however the table is not showing the classes that have no students in them. Is there someway I can remedy this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mandingo Jones View Post
    I have two tables, student and classes. In the classes table I have classes that students don't belong to. I want to display all of my classes in a query and the students who belong to those classes. however the table is not showing the classes that have no students in them. Is there someway I can remedy this?
    use a LEFT OUTER JOIN

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    I tried the left outer join and got the same result I always get which is the classes and students matched with them. The other classes did not show up. here is my code:

    Select class.class_description, student.Last_name, student.First_name
    From student Left Outer Join
    class ON student.class_title = class.class_title

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you are not familiar with outer joins?

    you should change this --
    Code:
    FROM student LEFT OUTER JOIN class
    to either this --
    Code:
    FROM student RIGHT OUTER JOIN class
    or this --
    Code:
    FROM class LEFT OUTER JOIN student
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please, please, please the latter.
    On RIGHT OUTER JOINS ...
    As it happens, for SQL Server there are certain optimisations the engine can make executing LOJs that it can't for ROJs. I don't know about other engines but the principle reasoning anyway is readability.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and i most certainly agree with you about never using RIGHT OUTER JOINs

    however, i wanted to give the original poster lots to think about (or look up), to understand the outer join concept a bit better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - I thought as much since we've posted about this before. I wasn't disagreeing with you, merely providing a complementary adjunct
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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