From: Daryl Richter <daryl@brandywine.com>
To: fmiddleton@verizon.net
To: pgsql-sql@postgresql.org
Subject: Re: showing multiple REFERENCE details of id fields in single
Date: Thu, 15 Sep 2005 09:43:28 -0400
Message-ID: <43297A80.9030008@brandywine.com> (raw)
In-Reply-To: <4328CA49.7030409@verizon.net>
References: <4328CA49.7030409@verizon.net>
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?
>
create table classes(
id SERIAL PRIMARY KEY,
course_title TEXT,
course_code TEXT );
go
insert into classes( course_title, course_code ) values( 'A', 'A1' );
insert into classes( course_title, course_code ) values( 'B', 'B1' );
insert into classes( course_title, course_code ) values( 'C', 'C1' );
go
create table class_prerequisite_bindings(
id SERIAL,
class_id INTEGER REFERENCES classes(id),
prerequisite INTEGER REFERENCES classes(id)
);
go
insert into class_prerequisite_bindings( class_id, prerequisite )
values( 1, 2 );
insert into class_prerequisite_bindings( class_id, prerequisite )
values( 1, 3 );
go
SELECT
a.id AS class_id,
c.id AS prerequisite_class
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 = 1;
go
> Ferindo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
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, fmiddleton@verizon.net
Subject: Re: showing multiple REFERENCE details of id fields in single
In-Reply-To: <43297A80.9030008@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