Thread: showing multiple REFERENCE details of id fields in single query that share the same table

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?

Ferindo

On Sep 14, 2005, at 8:11 PM, 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?
>
> Ferindo

This isn't the prettiest query in the world, but it's a starting
point. The RIGHT JOIN allows you to include the columns you want for
prerequisites that don't themselves have prerequisites.

SELECT *
FROM class_prerequisite_bindings, classes
WHERE class_prerequisite_bindings.class_id = 64
AND class_prerequisite_bindings.class_id = classes.id
UNION ALL
SELECT *
FROM class_prerequisite_bindings    RIGHT JOIN classes    ON classes.id = class_prerequisite_bindings.class_id
WHERE classes.id = (    SELECT prerequisite    FROM class_prerequisite_bindings    WHERE class_id = 64
);

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

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

SELECTa.id AS class_id,c.id AS prerequisite_class
FROMclasses a
JOIN    class_prerequisite_bindings b ON b.class_id = a.id
JOIN    classes c ON c.id = b.prerequisite
WHEREa.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

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
REFERENCESclasses(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