Message-ID: <43297A80.9030008@brandywine.com>
Date: Thu, 15 Sep 2005 09:43:28 -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: fmiddleton@verizon.net, pgsql-sql@postgresql.org
Subject: Re: showing multiple REFERENCE details of id fields in single
References: <4328CA49.7030409@verizon.net>
In-Reply-To: <4328CA49.7030409@verizon.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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