Thread: showing multiple reference details from single query

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

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