On Sep 14, 2005, at 8:11 PM, Ferindo Middleton Jr wrote:
I have a table which has two id fields which REFERENCE data back at
another table. It's setup like this:
class_prerequisite_bindings(id SERIAL, class_id INTEGER
REFERENCES classes(id), prerequisite INTEGER REFERENCES classes
(id))
The classes table is like this:
classes(id SERIAL, course_title TEXT, course_code TEXT)
I have the following query:
SELECT * FROM class_prerequisite_bindings, classes WHERE
class_prerequisite_bindings.class_id = 64 AND
class_prerequisite_bindings.class_id = classes.id;
If I run the query above, the result will only give me info about
the class_id field matching id 64 back in the classes table.
PROBLEM: I want this query to also show the info about the
prerequisite field which would also have info at the classes table.
This query will only show the course_title and course_code of the
class_id but I need this for the prerequisite field as well. I
think I need to do a JOIN, but I don't understand how. How can I do
this?
Ferindo
This isn't the prettiest query in the world, but it's a starting
point. The RIGHT JOIN allows you to include the columns you want for
prerequisites that don't themselves have prerequisites.
SELECT *
FROM class_prerequisite_bindings, classes
WHERE class_prerequisite_bindings.class_id = 64
AND class_prerequisite_bindings.class_id = classes.id
UNION ALL
SELECT *
FROM class_prerequisite_bindings
RIGHT JOIN classes
ON classes.id = class_prerequisite_bindings.class_id
WHERE classes.id = (
SELECT prerequisite
FROM class_prerequisite_bindings
WHERE class_id = 64
);
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
