Skip site navigation (1) Skip section navigation (2)

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

From: Ferindo Middleton Jr <fmiddleton(at)verizon(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: showing multiple REFERENCE details of id fields in single query that share the same table
Date: 2005-09-15 01:11:37
Message-ID: 4328CA49.7030409@verizon.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Thomas O'ConnellDate: 2005-09-15 05:16:12
Subject: Re: showing multiple REFERENCE details of id fields in single query that share the same table
Previous:From: Tom LaneDate: 2005-09-14 17:08:04
Subject: Re: CREATE TEMPORARY TABLE ON COMMIT DROP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group