Uttam: Your're great! But there's still one thing I haven't mentioned (my fault)...
If I add more childs to the same node_depth and sets SubProjectB NOT to inherit, then
the query fails...
I now have these records:
project_group_id ref_project_group_id name root_project_group_id
node_depth inherit
10 0 ProjectA 0
0 0
57 10 SubProjectA 10
1 1
59 57 SubProjectB 10
2 0
60 59 SubProjectC 10
3 1
63 59 SubProject_1 10
3 1
62 60 SubProjectD 10
4 1
64 63 SubProject_2 10
4 1
This would look somehting like this in a treeview:
ProjectA (10) <- node_depth = 0 (root)
\
SubProjectA (57) <- node_depth = 1
\
SubProjectB (59) <- node_depth = 2
/ \
SubProject_1 (63) SubProjectC (60) <- node_depth = 3
/ \
SubProject_1 (64) SubProjectD (62) <- node_depth = 4
But if running your query against the database I get the following output:
project_group_id ref_project_group_id name root_project_group_id
node_depth inherit
10 0 ProjectA 0
0 0
57 10 SubProjectA 10
1 1
62 60 SubProjectD 10
4 1
64 63 SubProject_2 10
4 1
What could be wrong?
Best Regards
Jakob Vedel Adeltoft, CTO
[EMAIL PROTECTED]
WebProof - www.webproof.dk
Tel +45 46 32 68 68
Fax +45 46 35 94 94
> -----Original Message-----
> From: Uttam [mailto:[EMAIL PROTECTED]
> Sent: Saturday, March 15, 2003 7:57 AM
> To: Jakob Vedel Adeltoft; [EMAIL PROTECTED]
> Subject: RE: JOIN on same table
>
>
> try this:
>
> SELECT PG1.*
> FROM project_group_list AS PG1 LEFT JOIN project_group_list AS PG2
> ON PG1.ref_project_group_id = PG2.project_group_id
> WHERE
> (PG1.root_project_group_id=10 AND PG1.inherit=1 AND
> PG2.inherit=1)
> OR (PG1.project_group_id=10)
> OR (PG1.ref_project_group_id=10)
> ORDER BY PG1.node_depth
>
> regards,
> -----Original Message-----
> From: Jakob Vedel Adeltoft [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 14, 2003 16:30
> To: [EMAIL PROTECTED]
> Subject: JOIN on same table
>
>
> Hi there,
> I have this table:
>
> project_group_id ref_project_group_id name
> root_project_group_id node_depth
> inherit
> 10 0 ProjectA 0 0 0
> 57 10 SubProjectA 10 1 1
> 59 57 SubProjectB 10 2 1
> 60 59 SubProjectC 10 3 0
> 62 60 SubProjectD 10 4 1
>
> project_group_id (int) = primary key.
> ref_project_group_id (int) = parent project (if root, then 0)
> name (char) = name of project
> root_project_group_id (int) = project_group_id for root project (if
> root, then 0)
> node_depth (int) = how far down in the tree this child is (if
> root, then
> 0)
> inherit (int) = determines if this project inherit information from
> parent (no=0, yes=1)
>
> This would look somehting like this in a treeview:
> ProjectA (10) <- node_depth =
> 0 (root)
> \
> SubProjectA (57) <-
> node_depth = 1
> \
> SubProjectB (59) <-
> node_depth = 2
> \
> SubProjectC (60) <-
> node_depth = 3
> \
> SubProjectD (62) <-
> node_depth = 4
>
> I now wan't all child records (inclusive root) for ProjectA
> that inherit
> information. This means that I only wan't records with
> project_group_id
> = 10, 57, 59, because 60 is NOT inheriting and it's child project (62)
> is then not able to inherit.
>
> I've made the follwoing query:
> SELECT PGL1.project_group_id, PGL1.ref_project_group_id, PGL1.name,
> PGL1.permission_inherit, PGL1.node_depth, PGL1.root_project_group_id
> FROM project_group_list PGL1
> LEFT JOIN project_group_list PGL2 ON PGL1.root_project_group_id =
> PGL2.root_project_group_id
> WHERE PGL2.root_project_group_id = 10
> AND PGL2.permission_inherit = 0
> AND PGL1.node_depth < PGL2.node_depth
> ORDER BY PGL1.node_depth
>
> But this only gives me 57 and 59, but not 10 (root). This query also
> have a problem, if all projects is inheriting and there is no records
> with inherit=0, then the query returns no results.
>
> What can I do to solve this problem? I hope someone can help me...
>
> /Jakob
>
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
