开发者

MySql Retrieve data from same table

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   |       开发者_如何学C1      |   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..


SELECT t1.id, t1.name AS Pname, 
    t2.parentId, t2.name as child, t2.id as childId
FROM myTbl t1
INNER JOIN myTbl t2 on t1.id = t2.parentId


Problem arises because your subquery is returning more than one row. Solution is to ensure that the subquery only ever returns one row, or to make sure it doesn't matter if it returns more

But these don't really give you the result that your table suggests you want

SELECT name AS Pname 
 FROM myTbl 
WHERE id = ( SELECT DISTINCT parentId 
               FROM myTbl 
              WHERE parentId = 1 
           )

OR

SELECT name AS Pname 
 FROM myTbl 
WHERE id IN ( SELECT DISTINCT parentId 
                FROM myTbl 
               WHERE parentId = 1 
            )
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜