Re: Joins within a table

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

On 6/13/07, Steve Lefevre <lefevre(dot)10(at)osu(dot)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/
___________________________________________