Howto get tree view? mysql

Question

I have the following tables :

specifications :

id | name
-----------
1  | hello
2  | world
3  | foo
4  | bar

properties :

name   | value  | specID
---------------------
status | finish | 1
parent | 2      | 1
status | work   | 2
parent | 3      | 2
status | ...    | 4
parent | 3      | 4

Now I want to say :

List all specifications under specID 3.

I don't now the levels down but the result must be:

id | name  | parent
------------------
 3 | foo   |  NULL
 2 | world | 3
 1 | hello | 2
 4 | bar   | 3

How can i do this in mysql?

Saharsh Shah · Answer 1 · 2013-12-16T07:54:34.523

1

Try this:

SELECT s.id, s.name, p.value AS parent
FROM specifications s 
LEFT JOIN properties p ON s.id = p.specID AND p.name = 'parent';

edited Dec 16 '13 at 07:54

answered Dec 16 '13 at 07:29

Saharsh Shah

28,687
8
48
83

there isn't value attribute ! – ktaria Dec 16 '13 at 08:50
@ktaria Value is a column in properties table – Saharsh Shah Dec 16 '13 at 08:52
It's possible to say, start at specification 4? I don't need a tree over all, i have to set a "start point". – Alexander G. Dec 16 '13 at 09:07

score 1 · Answer 2 · answered Dec 16 '13 at 07:47

1

You can try it like this

SELECT 
    s.id,
    s.name,
    IFNULL(p.value,0) as parent
FROM  specifications s
LEFT JOIN properties p 
    ON p.specID = s.id 
    AND p.name  ='parent'
ORDER BY parent

Output

| ID |  NAME | PARENT |
|----|-------|--------|
|  3 |   foo |      0 |
|  1 | hello |      2 |
|  2 | world |      3 |
|  4 |   bar |      3 |

Fiddle

answered Dec 16 '13 at 07:47

Muhammad Raheel

19,823
7
67
103

It's possible to say, start at specification 4? I don't need a tree over all, i have to set a "start point". – Alexander G. Dec 16 '13 at 09:06
can you explain what you mean? – Muhammad Raheel Dec 16 '13 at 09:27
Imagine in my table "specification" have thousands of rows. Different trees of specifications. I need to specify "show me the tree, start at specification 3 as root". do you understand? – Alexander G. Dec 16 '13 at 09:41
@AlexanderG. take a look at [this](http://stackoverflow.com/questions/12948009/finding-all-parents-in-mysql-table-with-single-query). I am sure this will help you – Muhammad Raheel Dec 16 '13 at 09:49
Unfortunately, i do not understand your link :/ – Alexander G. Dec 16 '13 at 10:37

score 1 · Answer 3 · answered Dec 17 '13 at 13:46

MySQL does not have any syntax constructs that allow you to write a recursive query. So, with this schema, there is no convenient way to retrieve a full tree - at least, not with a SELECT query.

What you could do is try to rework your schema to a "materialized path", or a "nested set" or a "nested interval" solution. See these papers for details:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ http://sigmod.acm.org/publications/sigmod-record/0506/p47-article-tropashko.pdf

Howto get tree view? mysql

3 Answers3

Fiddle