Re: Using joins on the same table

[Ramalingam Chelliah]

Hi,
I tried your idea....

Same table can be 'join'ed but atleast with one alias name...

Subqueries are supported only in versions >=4.1

I have defined table JoinT like ...
CREATE TABLE JoinT(ID integer NOT NULL,MID integer,Name varchar(10));
ID -Child
MID -Master ID/Parent ID
Name --name of item(folder)

Let us take Unix hierarchy

Dir <-----------> Parent <-------------> Name

/ <-------> / <------> Root
dev <------> / <-------> Device
home <------> / <-------> Home directory
rlingam <------> /home <-------> Rlingam's home dir
ram <------> /home <------> Ram's home dir


mysql> SELECT * FROM JoinT;
+----+------+---------+
| ID | MID | Name |
+----+------+---------+
| 10 | 10 | Root |
| 11 | 10 | Home |
| 12 | 10 | Dev |
| 13 | 11 | Rlingam |
| 14 | 11 | Ram |
+----+------+---------+

5 rows in set (0.00 sec)

Now Root has three Children ( Dev , Home , Root (itself..Say))
Home has two children ( Rlingam , Ram )

Given this query I am getting the result set as you expected ...

mysql > SELECT J.Name ,J.ID,COUNT(*)
FROM JoinT as J
LEFT OUTER JOIN JoinT
ON J.ID=JoinT.MID
GROUP BY J.ID ;

+-----+---------+--------------+
| ID | Name | COUNT(*) |
+-----+---------+--------------+
| 10 | Root | 3 |
| 11 | Home | 2 |
| 12 | Dev | 1 |
| 13 | Rlingam | 1 |
| 14 | Ram | 1 |
+----+----------+-------------+

5 rows in set (0.00 sec)


Hope this is useful ,

Regards,
Ram.


P.S :

For Your Information ,

mysql> SELECT * FROM
JoinT as J
LEFT OUTER JOIN
JoinT
ON J.ID=JoinT.MID ;

+----+------+---------+-------+-------+----------+
| ID | MID | Name | ID | MID | Name |
+----+------+---------+-------+-------+----------+
| 10 | 10 | Root | 10 | 10 | Root |
| 10 | 10 | Root | 11 | 10 | Home |
| 10 | 10 | Root | 12 | 10 | Dev |
| 11 | 10 | Home | 13 | 11 | Rlingam |
| 11 | 10 | Home | 14 | 11 | Ram |
| 12 | 10 | Dev | NULL | NULL | NULL |
| 13 | 11 | Rlingam | NULL | NULL | NULL |
| 14 | 11 | Ram | NULL | NULL | NULL |
+----+------+---------+------+------+---------+

8 rows in set (0.00 sec)

Regards,
Ram.








We Learn the Most When we have to Invent