pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedshowing multiple REFERENCE details of id fields in single query that share the same table
4+ messages / 3 participants
[nested] [flat]
* showing multiple REFERENCE details of id fields in single query that share the same table
@ 2005-09-15 01:11 Ferindo Middleton Jr <fmiddleton@verizon.net>
0 siblings, 2 replies; 4+ messages in thread
From: Ferindo Middleton Jr @ 2005-09-15 01:11 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: showing multiple REFERENCE details of id fields in single query that share the same table
@ 2005-09-15 05:16 Thomas O'Connell <tfo@sitening.com>
parent: Ferindo Middleton Jr <fmiddleton@verizon.net>
1 sibling, 0 replies; 4+ messages in thread
From: Thomas O'Connell @ 2005-09-15 05:16 UTC (permalink / raw)
To: fmiddleton@verizon.net; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: showing multiple REFERENCE details of id fields in single
@ 2005-09-15 13:43 Daryl Richter <daryl@brandywine.com>
parent: Ferindo Middleton Jr <fmiddleton@verizon.net>
1 sibling, 1 reply; 4+ messages in thread
From: Daryl Richter @ 2005-09-15 13:43 UTC (permalink / raw)
To: fmiddleton@verizon.net; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: showing multiple REFERENCE details of id fields in single
@ 2005-09-16 16:16 Daryl Richter <daryl@brandywine.com>
parent: Daryl Richter <daryl@brandywine.com>
0 siblings, 0 replies; 4+ messages in thread
From: Daryl Richter @ 2005-09-16 16:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-16 16:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-15 01:11 showing multiple REFERENCE details of id fields in single query that share the same table Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-15 05:16 ` Thomas O'Connell <tfo@sitening.com>
2005-09-15 13:43 ` Re: showing multiple REFERENCE details of id fields in single Daryl Richter <daryl@brandywine.com>
2005-09-16 16:16 ` Re: showing multiple REFERENCE details of id fields in single Daryl Richter <daryl@brandywine.com>
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