Message-ID: <432AEFFB.6080205@brandywine.com>
Date: Fri, 16 Sep 2005 12:16:59 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: showing multiple REFERENCE details of id fields in single
References: <4328CA49.7030409@verizon.net> <43297A80.9030008@brandywine.com>
	<432A21D3.4050806@verizon.net>
In-Reply-To: <432A21D3.4050806@verizon.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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