From: Daryl Richter <daryl@brandywine.com>
To: pgsql-sql@postgresql.org
Subject: Re: showing multiple REFERENCE details of id fields in single
Date: Fri, 16 Sep 2005 12:16:59 -0400
Message-ID: <432AEFFB.6080205@brandywine.com> (raw)
In-Reply-To: <432A21D3.4050806@verizon.net>
References: <4328CA49.7030409@verizon.net>
<43297A80.9030008@brandywine.com>
<432A21D3.4050806@verizon.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: daryl@brandywine.com
Subject: Re: showing multiple REFERENCE details of id fields in single
In-Reply-To: <432AEFFB.6080205@brandywine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox