[thelist] [sql] cross join question I believe...

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Paul Bennett" <paul at teltest.com>
Subject: [thelist] [sql] cross join question I believe...


: Hi there,
: I have 3 tables
: One holds "course" information
: One holds "career" information
: 
: A course can have many careers associated with it
: A career can be applied to many courses
: 
: I have a table "in between" them which links entries for a course id 
: with entries for a career id, this way I can have a list of courses on 
: one hand and a list of careers on the other and have a simple key table 
: int the middle with references to corresponding fields for both.
: 
: a) Is this the right thing to do?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I believe so.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I need to then (in one query because I am LOVING joins at the moment) 
: get all information for a "course" and all the "careers" which are 
: associated with it.
: I am comfortable with left joins, but how do i say "get all course 
: information, as well as all careers from the careers table where the 
: career id in the "key" table corresponds to the "course id" in the key 
: table?
: 
: Does that make sense?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT
    a.CoursesField1,
    a.CoursesField2,
    a.CoursesFieldn,
    c.CareersName
FROM
    Courses AS a
INNER JOIN
    CareersCourses AS b
ON
    a.CoursesID = b.CoursesID
INNER JOIN
    Careers AS c
ON
    b.CareersID = c.CareersID

Cheers
Ken