pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedshowing multiple reference details from single query
2+ messages / 2 participants
[nested] [flat]
* showing multiple reference details from single query
@ 2005-09-11 22:37 Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-11 23:33 ` Re: showing multiple reference details from single query Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Ferindo Middleton Jr @ 2005-09-11 22:37 UTC (permalink / raw)
To: pgsql-sql
I have a table (table_one) with two columns, both of which are integers
which reference the same column (on a one-to-many relationship) row back
at another table (table_two) which has more detailed info on these
integer columns:
table_one has the following columns:
id (SERIAL), column_one (INTEGER REFERENCES table_two(id)), column_two
(INTEGER REFERENCES table_two(id))
table_two has the following columns:
id (SERIAL), more_detailed_info (TEXT)
How can I write a single query which will can uniquely identify in the
result the separated values of the more_detailed_info field of
table_two? For instance, if I write the following query:
SELECT * FROM table_one WHERE id = 4 AND table_one.column_one =
table_two.id;
I'll get rows which will list the more_detailed_info fields one time for
each match but table_two has more_detailed_info for both column_one and
column_two. However the query above will only be able to show the
more_detailed_info field for column_one. How can I show the
more_detailed_info field for column_two as well on the same row,
simultaneously with that of column_one
Sorry if this is confusing. I don't know of all the technical jargon
which involves my question above.
Ferindo
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: showing multiple reference details from single query
2005-09-11 22:37 showing multiple reference details from single query Ferindo Middleton Jr <fmiddleton@verizon.net>
@ 2005-09-11 23:33 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-09-11 23:33 UTC (permalink / raw)
To: Ferindo Middleton Jr <fmiddleton@verizon.net>; +Cc: pgsql-sql
On Sun, Sep 11, 2005 at 06:37:52PM -0400, Ferindo Middleton Jr wrote:
> table_one has the following columns:
> id (SERIAL), column_one (INTEGER REFERENCES table_two(id)), column_two
> (INTEGER REFERENCES table_two(id))
>
> table_two has the following columns:
> id (SERIAL), more_detailed_info (TEXT)
>
> How can I write a single query which will can uniquely identify in the
> result the separated values of the more_detailed_info field of
> table_two?
I think you're looking for something like this:
SELECT t1.id,
t1.column_one, t2a.more_detailed_info,
t1.column_two, t2b.more_detailed_info
FROM table_one AS t1
JOIN table_two AS t2a ON t2a.id = t1.column_one
JOIN table_two AS t2b ON t2b.id = t1.column_two;
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-09-11 23:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-11 22:37 showing multiple reference details from single query Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-11 23:33 ` Michael Fuhr <mike@fuhr.org>
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