CODEWITHSUNDEEP
phpmysqldatabase
Martian.titan
Martian.titan

Reputation: 466

PHP MySql get data from a table and left join to the same table

I have this table names MyLevels.

 id  | level | ParentLevelId
 ---------------------
 1   | basic | 1
 2   | silver| 1
 3   | gold  | 2
 4   | stone | 3
 5   | wood  | 2

on this table every level has a parent level. For example the "gold" level parent id is 2. It's mean the parent level is "silver".
So I need a query to get the below in a html table: Parent level comes from ParentLevelId.

level  | parent level | distance From Basic
-------------------------------------------
basic  | basic        | 0
silver | basic        | 1
gold   | silver       | 2

The distance from basic reference to the number that each level is far from basic level.
For example the wood level id is 5 and 5 - 1 = 4. which means the it's 4 level far from basic level.
This is the only code I have:

$conn->prepare('SELECT id, level, ParentLevelId FROM MyLevels');

Any help appreciated.

Thanks

Upvotes: 1

Views: 65

Answers (3)

TimBrownlaw
TimBrownlaw

Reputation: 5507

Just another version to add to the fun.

I thought the op was wanting to get the Distance from basic, which in this case is id = 1.

Anyway, another way to write this is...

SELECT 
      b.level AS level, 
      a.level AS parent_level,
      b.id-1 AS distance_from_basic 
      FROM MyLevels a
        JOIN MyLevels b ON a.id = b.ParentLevelId
            ORDER BY b.id

This was tested against the desired results provided by the OP.

The results:

Level   Parent Level    Distance From Basic
basic   basic                  0
silver  basic                  1
gold    silver                 2
stone   gold                   3
wood    silver                 4

Upvotes: 1

krishn Patel
krishn Patel

Reputation: 2599

can you try this ? 

   SELECT a.level, b.level ParentLeve , IFNULL(b.id,0) FROM 
MyLevels a left join MyLevels b on a.id=b.ParentLevelId

Upvotes: 1

Naincy
Naincy

Reputation: 2943

Please try this.

select c.level as level, p.level as parent_level, c.id-p.id as distance_from_parent
from MyLevels AS p
JOIN MyLevels AS c on p.id = c.ParentLevelId

Upvotes: 1

Related Questions