MySql Retrive data from same table.
- by Muhammad Sajid
Hi,
I have a table which contains id, name, parentId of Top level Menus & their children like:
--------------------------------------
    id    |    name    |    parentId 
--------------------------------------
    1     |    Color   |       0
--------------------------------------
    2     |    Flower  |       0
--------------------------------------
    3     |    Red     |       1
--------------------------------------
    4     |    pink    |       1
--------------------------------------
    5     |    Rose    |       2
--------------------------------------
    6     |    Lily    |       2
--------------------------------------
And I want to fetch these record some thing that the resultant array must be like
---------------------------------------------------------------
    id    |    Pname    |    parentId  |  child    |   childId
---------------------------------------------------------------
    1     |    Color   |       1      |   Red     |    3
---------------------------------------------------------------
    2     |    Color   |       1      |   Pink    |    4
---------------------------------------------------------------
    3     |    Flower  |       2      |   Rose    |    5
---------------------------------------------------------------
    4     |    Flower  |       2      |   Lily    |    6
---------------------------------------------------------------
my query was like:
SELECT name AS Pname FROM myTbl WHERE id = (SELECT parentId FROM myTbl WHERE parentId = 1 )
but mysql say #1242 - Subquery returns more than 1 row
Could anyone solve it.
Thanks..