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
)
精彩评论