sub query or Self join query For the mysql table Which works like recursive function?

Question

I have Following structure in my table with the data

enter image description here

Now From Above Data let say if I want to find all sub organization where organization Id is given..then how can find that .

here how can i make it show Reseller A,Reseller B,Advertiser A,Advertiser B,when organization Id = 10707(Main Organization)

My table name is organization_ and there is

parentorganizationId

in each row which describes the parentorganization of that particular organization.

So when I give id of main organization say 10707 then it should return me all suborganizations with that organization id..

please review your question there is a lot of detail missing this question will not be answered in its current form/ — Namphibian, Jan 07 '13 at 06:54

ypercubeᵀᴹ · Accepted Answer · 2013-01-07T09:52:46.083

2

There are various models for Hierarchical data. It seems you are using both "Adjacency List" and "Path Enumeration" in your table. You can read about them in Bill Karwin's slideshow, which is discussing various models and implementations in MySQL: Models for Hierarchical data with SQL

So, using the treePath column, the query you need is rather simple:

SELECT
    GROUP_CONCAT(t.name) AS SubOrganizations
FROM
    tableX AS t
  JOIN
    tableX AS p
      ON  t.treePath LIKE CONCAT( p.treePath, '%') 
      AND t.organizationId <> p.organizationId 
WHERE
    p.organizationId = 10707 ;

edited Jan 07 '13 at 09:52

answered Jan 07 '13 at 09:46

ypercubeᵀᴹ

113,259
19
174
235

its giving null value...1 row fetched but with null value – BhavikKama Jan 07 '13 at 10:13
Thnx.it worked..but what if i want to concat the root organization with the suborganization too? – BhavikKama Jan 07 '13 at 10:23
i want to use this query as a subquery in one query..so i dont want to concat it...can u please tell me about that? – BhavikKama Jan 07 '13 at 10:43
If you don't want to group concat the names, just `SELECT t.organizationId, t.name ...` (without changing anything else). – ypercubeᵀᴹ Jan 07 '13 at 10:44
And if you want to concat the "main" organization with the suborganizations, just remove the `AND t.organizationId <> p.organizationId` line – ypercubeᵀᴹ Jan 07 '13 at 10:46

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

In short, unlike other RDMBS (SQL Server, Oracle), MySql doesn't support recursive queries.

Couple of helpful links on the subject for you from this site:

edited May 23 '17 at 12:08

Community

1
1

answered Jan 07 '13 at 07:10

peterm

91,357
15
148
157

`s/unlike some other/unlike real` – ta.speot.is Jan 07 '13 at 07:13
can anyone give me example..even if it can be done by self join or some other way – BhavikKama Jan 07 '13 at 09:35
The first link of the first link leads you to http://dba.stackexchange.com/a/7161 – ta.speot.is Jan 07 '13 at 09:43

sub query or Self join query For the mysql table Which works like recursive function?

2 Answers2