INNER JOIN ?

[RecoilUK]

Hi guys

 

I have the following tables for a project i,m working on but for now i,m just interested in the modules table and the self referencing relationship.

 

So far I have managed to build this query ...

 

SELECT parent.module_name AS Parent, child.module_name AS Child
FROM modules AS child
LEFT JOIN modules AS parent ON child.has_parent = parent.module_id;

 

Which at the moment gives me the following result set

 

+--------+--------+

| Parent | Child      |

+--------+--------+

| NULL  | admin    |

| admin  | users    |

| admin  | modules |

+--------+--------+

 

Now, the problem is, if a child has a null parent, it should be a parent, ready for a child, and having limited experience with MySQL and SQL in general, i,m not even sure if this is possible to do, which is where I could do with a bit of help.

 

Thanks guys

 

[attachment deleted by admin]

[Barand]

I'm not clear what the problem is. The join structure is a common one. What are you trying to achieve?

 

What is "this" in "not even sure if this is possible to do"

[RecoilUK]

Hi

 

I,ll try to explain ...

 

Admin is a module, its also a parent of Users.

Users is a sub module of Admin, so its a child.

 

Parent = Module

Child = Sub Module

 

Should have just called them Module and Sub Module in the first place.

 

Admin is not a child of anything.

 

Its for a User Permission system as detailed in this thread http://www.phpfreaks.com/forums/index.php/topic,206313.0.html in which I asked for advice, and decided to give it try and see what happens.

 

Modules are different areas of a website, with a group of permissions associated with them.

 

I just want to get a complete list of Modules and any sub modules if they exist.

 

So the admin in child from the result set, shouldnt be there, it should be in parent.

 

Thanks

[Barand]

try

SELECT parent.module_name AS Parent, child.module_name AS Module_name
FROM modules AS child
LEFT JOIN modules AS parent ON child.has_parent = parent.module_id;

 

Now it isn't a "child" any more, it's just a module with no parent as it's top of the tree.