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
