pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Self Join Help
5+ messages / 2 participants
[nested] [flat]

* Self Join Help
@ 2003-04-19 05:07  Gerard Samuel <gsam@trini0.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Gerard Samuel @ 2003-04-19 05:07 UTC (permalink / raw)
  To: pgsql-php

Im trying to figure out Self Joins with PostgreSQL.  The output of the 
second SQL is correct, because of the where a.id = b.pid,
but I would like to return all rows that are part of the tree.
i.e.
Foo
Apache
 - PHP
XHTML
News
 - World News
 - Tech News

Any help would be appreciated.
Thanks.

test=# select * from topics;
        id        |       pid        | topicname
------------------+------------------+------------
 AFAdDFoAPNX6wKbr | 0                | Foo
 AFAdDFoAPgTi9tAE | 0                | Apache
 AFAdDFoAPgTjCa4V | AFAdDFoAPgTi9tAE | PHP
 AFAdDFoAPlv1ENRn | 0                | XHTML
 AFAdDFoAPoSEWZaq | 0                | News
 AFAdDFoAPoSEaRPV | AFAdDFoAPoSEWZaq | World News
 AFAdDFoAPoSEee5_ | AFAdDFoAPoSEWZaq | Tech News
(7 rows)

test=# select a.topicname as parent, b.topicname as child from topics as 
a, topics as b where a.id = b.pid;
 parent |   child
--------+------------
 Apache | PHP
 News   | Tech News
 News   | World News
(3 rows)




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Self Join Help
@ 2003-04-19 06:38  apz <apz@nofate.com>
  parent: Gerard Samuel <gsam@trini0.org>
  0 siblings, 1 reply; 5+ messages in thread

From: apz @ 2003-04-19 06:38 UTC (permalink / raw)
  To: Gerard Samuel <gsam@trini0.org>; +Cc: pgsql-php

Gerard Samuel wrote:
> Im trying to figure out Self Joins with PostgreSQL.  The output of the 
> second SQL is correct, because of the where a.id = b.pid,
> but I would like to return all rows that are part of the tree.
> test=# select * from topics;
>        id        |       pid        | topicname
> ------------------+------------------+------------
> AFAdDFoAPNX6wKbr | 0                | Foo
> AFAdDFoAPgTi9tAE | 0                | Apache
> AFAdDFoAPgTjCa4V | AFAdDFoAPgTi9tAE | PHP
> AFAdDFoAPlv1ENRn | 0                | XHTML
> AFAdDFoAPoSEWZaq | 0                | News
> AFAdDFoAPoSEaRPV | AFAdDFoAPoSEWZaq | World News
> AFAdDFoAPoSEee5_ | AFAdDFoAPoSEWZaq | Tech News
> (7 rows)
> 
> test=# select a.topicname as parent, b.topicname as child from topics as 
> a, topics as b where a.id = b.pid;
> parent |   child
> --------+------------
> Apache | PHP
> News   | Tech News
> News   | World News
> (3 rows)

do you mean return also root nodes? You could just add

insert into topics (id, topicname) values (0, 'root');

and then you should be getting

test=# select a.topicname as parent, b.topicname as child from topics as
  a, topics as b where a.id = b.pid;

  parent |   child
--------+------------
  root   | Apache
  root   | News
  root   | Foo
  root   | XHTML
  Apache | PHP
  News   | Tech News
  News   | World News


or, if you dont want to add a ficticious root node you could do a left 
join (if you can do left self joins, dont see a reason why not, but 
never did it):

test=# select a.topicname as parent, b.topicname as child from topics as 
a left join topics as b on a.id = b.pid;

  parent |   child
--------+------------
  XHTML  | null
  Foo    | null
  Apache | PHP
  News   | Tech News
  News   | World News


but then, your self referencing querry starts going into the idea of 
recursive select statements. I have little knowledge in this, MS-SQL 
does not have true recursive selects (you can string up bunch of left 
joins, but its a workaround hack). Oracle and I think db2 do support 
recursive selects, but only to a certain level (Oracle recurses up to 
32levels I think), I wouldnt mind hearing how recursive Select would 
work in your case:

	by recursive I mean I want to select all nodes who have a
	specific node above the tree (be it parent/grand parent/
	grand grand parent, etc).


/apz,   If your aim in life is nothing, you can't miss.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Self Join Help
@ 2003-04-19 06:49  apz <apz@nofate.com>
  parent: apz <apz@nofate.com>
  0 siblings, 1 reply; 5+ messages in thread

From: apz @ 2003-04-19 06:49 UTC (permalink / raw)
  To: ; +Cc: Gerard Samuel <gsam@trini0.org>; pgsql-php

this is second time I post to a forum, and second time I correct 
myself... ugh, I should delay posting by 15 minutes, or stop re-reading 
my emails after posting... ;D

anyways:

apz wrote:
> test=# select a.topicname as parent, b.topicname as child from topics as 
> a left join topics as b on a.id = b.pid;
> 
>  parent |   child
> --------+------------
>  XHTML  | null
>  Foo    | null
>  Apache | PHP
>  News   | Tech News
>  News   | World News

this actually should return:


test=# select a.topicname as parent, b.topicname as child from topics as
a left join topics as b on a.id = b.pid;

  parent     | child
------------+------------
  XHTML      | null
  Foo        | null
  Apache     | PHP
  News       | Tech News
  News       | World News
  PHP        | null
  Tech News  | null
  World News | null


the querry with left join should return child=null if a node is a leaf.
so XHTML and PHP return child as null because neither have any nodes 
underneath.
this also should mean that topicname should not allow null values, not 
to confuse ourselves further on.

so two ways are:
  - add one node which is always root, use your querry
  - use left join, when no child then child returns as null



/apz,  You can always tell luck from ability by its duration.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Self Join Help
@ 2003-04-19 14:01  Gerard Samuel <gsam@trini0.org>
  parent: apz <apz@nofate.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Gerard Samuel @ 2003-04-19 14:01 UTC (permalink / raw)
  To: apz <apz@nofate.com>; +Cc: pgsql-php

Thanks for you help thus far.
The final goal would be to achieve results like ->
   parent   |   child
------------+------------
 Foo        |
 Apache     | PHP
 XHTML      |
 News       | Tech News
 News       | World News

Im playing with the SQL to see if its possible, but any insight would be 
appreciated.
Thanks again.

apz wrote:

> this is second time I post to a forum, and second time I correct 
> myself... ugh, I should delay posting by 15 minutes, or stop 
> re-reading my emails after posting... ;D
>
> anyways:
>
> apz wrote:
>
>> test=# select a.topicname as parent, b.topicname as child from topics 
>> as a left join topics as b on a.id = b.pid;
>>
>>  parent |   child
>> --------+------------
>>  XHTML  | null
>>  Foo    | null
>>  Apache | PHP
>>  News   | Tech News
>>  News   | World News
>
>
> this actually should return:
>
>
> test=# select a.topicname as parent, b.topicname as child from topics as
> a left join topics as b on a.id = b.pid;
>
>  parent     | child
> ------------+------------
>  XHTML      | null
>  Foo        | null
>  Apache     | PHP
>  News       | Tech News
>  News       | World News
>  PHP        | null
>  Tech News  | null
>  World News | null
>
>
> the querry with left join should return child=null if a node is a leaf.
> so XHTML and PHP return child as null because neither have any nodes 
> underneath.
> this also should mean that topicname should not allow null values, not 
> to confuse ourselves further on.
>
> so two ways are:
>  - add one node which is always root, use your querry
>  - use left join, when no child then child returns as null
>
>
>
> /apz,  You can always tell luck from ability by its duration.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Self Join Help
@ 2003-04-19 16:25  Gerard Samuel <gsam@trini0.org>
  parent: Gerard Samuel <gsam@trini0.org>
  0 siblings, 0 replies; 5+ messages in thread

From: Gerard Samuel @ 2003-04-19 16:25 UTC (permalink / raw)
  To: apz <apz@nofate.com>; +Cc: pgsql-php

Presto ->
select a.topicname as parent, b.topicname as child from topics as a left 
join topics as b on a.id = b.pid where a.pid = 0;
 parent |   child
--------+------------
 Foo    |
 Apache | PHP
 XHTML  |
 News   | World News
 News   | Tech News
(5 rows)

Thanks for pointing me in the right direction....

Gerard Samuel wrote:

> Thanks for you help thus far.
> The final goal would be to achieve results like ->
>   parent   |   child
> ------------+------------
> Foo        |
> Apache     | PHP
> XHTML      |
> News       | Tech News
> News       | World News
>
> Im playing with the SQL to see if its possible, but any insight would 
> be appreciated.
> Thanks again.
>
> apz wrote:
>
>> this is second time I post to a forum, and second time I correct 
>> myself... ugh, I should delay posting by 15 minutes, or stop 
>> re-reading my emails after posting... ;D
>>
>> anyways:
>>
>> apz wrote:
>>
>>> test=# select a.topicname as parent, b.topicname as child from 
>>> topics as a left join topics as b on a.id = b.pid;
>>>
>>>  parent |   child
>>> --------+------------
>>>  XHTML  | null
>>>  Foo    | null
>>>  Apache | PHP
>>>  News   | Tech News
>>>  News   | World News
>>
>>
>>
>> this actually should return:
>>
>>
>> test=# select a.topicname as parent, b.topicname as child from topics as
>> a left join topics as b on a.id = b.pid;
>>
>>  parent     | child
>> ------------+------------
>>  XHTML      | null
>>  Foo        | null
>>  Apache     | PHP
>>  News       | Tech News
>>  News       | World News
>>  PHP        | null
>>  Tech News  | null
>>  World News | null
>>
>>
>> the querry with left join should return child=null if a node is a leaf.
>> so XHTML and PHP return child as null because neither have any nodes 
>> underneath.
>> this also should mean that topicname should not allow null values, 
>> not to confuse ourselves further on.
>>
>> so two ways are:
>>  - add one node which is always root, use your querry
>>  - use left join, when no child then child returns as null
>>
>>
>>
>> /apz,  You can always tell luck from ability by its duration.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-04-19 16:25 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-19 05:07 Self Join Help Gerard Samuel <gsam@trini0.org>
2003-04-19 06:38 ` apz <apz@nofate.com>
2003-04-19 06:49   ` apz <apz@nofate.com>
2003-04-19 14:01     ` Gerard Samuel <gsam@trini0.org>
2003-04-19 16:25       ` Gerard Samuel <gsam@trini0.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