Yup! Maybe I haven't explained me correct, but yes, that's what I wan't. This is because as soon as a project is NOT inheriting, then all of it's childs are then also not able to inherit from the original root, because the project that's NOT inheriting breaks the node...
But the childs are still able to inherit from the parent that breaks the node - but then they only inherit information from this parent and not from the root. 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 12:59 PM > To: Jakob Vedel Adeltoft > Subject: RE: JOIN on same table > > > you want only following records, i.e. excluding all childs of > SubProjectB because SubProjectB is not inheriting? > > 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 > > > -----Original Message----- > From: Jakob Vedel Adeltoft [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 15, 2003 15:28 > To: Uttam; [EMAIL PROTECTED] > Subject: RE: JOIN on same table > > > 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
