Re: showing multiple REFERENCE details of id fields in single

Ferindo Middleton Jr wrote:
> Thanks Daryl. The query you wrote works perfectly for the results I was
> trying to get at. I modified it a little because the real thing I needed
> to be able to see is the course_title and not the the id of the courses:
>
> SELECT
> a.course_title AS class_title,
> c.course_title AS prerequisite_class_title
> FROM
> classes a
> JOIN class_prerequisite_bindings b ON b.class_id = a.id
> JOIN classes c ON c.id = b.prerequisite
> WHERE
> a.id = ?;
>
> I don't quite understand how this query is working completely but maybe
> I'll understand after studying it a little longer. Thank you for your help.
>
> Ferindo
>

No problem at all. In future, you should try to place your responses in
the body of the reply, it makes it easier for others to read later.

Perhaps if you envision it this way?

classes.id <-> class_prerequisite_bindings.id
class_prerequisite_bindings.prerequisite <-> classes.id

As a side note, your db schema would be cleaner if you removed the
unneeded surrogate key id from the class_prerequisite_bindings table,
like so:

create table class_prerequisite_bindings(
class_id INTEGER REFERENCES classes(id),
prerequisite INTEGER REFERENCES classes(id),
constraint pk_cpb primary key( class_id, prerequisite )
);

This will, for example, prevent a class from having duplicate
prerequisite classes.

Good Luck!

[snipped]

--
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776