Обсуждение: Joins within a table
Hello all - I'm trying to do a JOIN within a table. In MySQL, I would do SELECT main_table.field, join_table.field FROM main_table LEFT JOIN main_table AS join_table ON join_table.id = main_table.parent_id Or something like that. However, in the Postgres documentation, I don't see anything on JOIN AS. How would I do a join within a table in Postgres? The table in question is a genetics table of viruses. The columns are id, strain, and parent_id. Since viruses only replicate, each strain has one parent. This way I can have one table and show entire tree of the relationship. It guarantees that I have only one parent per virus. The only other way I can think of doing it would be to have two tables: table strains ------------ id strain table relationships -------------- parent_id child_id But then I think I would have to implement some constraints so that I don't get more than one parent per child.
--- Steve Lefevre <lefevre.10@osu.edu> wrote: > Hello all - > > I'm trying to do a JOIN within a table. In MySQL, I would do > > SELECT main_table.field, join_table.field > FROM main_table > LEFT JOIN main_table AS join_table ON join_table.id = main_table.parent_id PostgreSQL conforms to the SQL standard when it comes to giving alias names to a single table and doesn't use MySQL's vendor specific extension: SELECT Instance1.field, Instance2.field FROM Main_table AS Instance1 LEFT OUTER JOIN Main_table AS Instance2 ON Instance1.id = Instance2.id; Here I am showing two alias names, but you could reduce this query to use only one if you wanted. Regards, Richard Broersma Jr.
It will probably work if you remove the AS.
SELECT main_table.field, join_table.field
FROM main_table
LEFT JOIN main_table join_table ON join_table.id = main_table.parent_id
FROM main_table
LEFT JOIN main_table join_table ON join_table.id = main_table.parent_id
On 6/13/07, Steve Lefevre <lefevre.10@osu.edu> wrote:
Hello all -
I'm trying to do a JOIN within a table. In MySQL, I would do
SELECT main_table.field, join_table.field
FROM main_table
LEFT JOIN main_table AS join_table ON join_table.id = main_table.parent_id
Or something like that.
However, in the Postgres documentation, I don't see anything on JOIN AS.
How would I do a join within a table in Postgres?
The table in question is a genetics table of viruses. The columns are
id, strain, and parent_id. Since viruses only replicate, each strain has
one parent. This way I can have one table and show entire tree of the
relationship. It guarantees that I have only one parent per virus.
The only other way I can think of doing it would be to have two tables:
table strains
------------
id
strain
table relationships
--------------
parent_id
child_id
But then I think I would have to implement some constraints so that I
don't get more than one parent per child.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.cas.edu.au/
___________________________________________
Steve Lefevre <lefevre.10@osu.edu> writes: > I'm trying to do a JOIN within a table. In MySQL, I would do > SELECT main_table.field, join_table.field > FROM main_table > LEFT JOIN main_table AS join_table ON join_table.id = main_table.parent_id Yeah, that's the same way you'd do a self-join in Postgres. > However, in the Postgres documentation, I don't see anything on JOIN AS. Where did you look? Maybe you are confusing the alias clause with the join clause --- they are two different constructs. regards, tom lane