MySQL nested queries how to select more than one row

Question

SELECT id, FIO, parent_id
FROM users
WHERE parent_id =
(
    SELECT id
    FROM users
    WHERE parent_id =
    (
        SELECT id
        FROM users
        WHERE id = 16
    )
)

So here I am making an hierarchy tree, first selecting the root parent, then the children's and so on to 24th level of depth.

The question is: How to select more than one column from the inner queries?

Because I need to get the other rows fields to display info like: name, surname, age

It looks like I can only get those columns of rows in the outer query (the topmost).

P.S.: I don't want to use joins because they generate duplicate fields.

Is there a solution?

Joins generate duplicate fields if you specify them to show up in the select clause. PS. 24 levels of depth looks like a humongous, untreatable query which pays out if you use a recursion to get data until the parent_id is null. Just saying — Alfabravo, Jul 09 '12 at 20:27
Please, provide your audience with an explanation of your *intend* of such a nested (24 times?) query. *What are you doing?* Do you want to find all the childs which are 24 steps away from a user? (all the grand-grand-grand-grand-.....-childs?) — Kaii, Jul 09 '12 at 20:47
http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter - here is the reason — Mister PHP, Jul 09 '12 at 21:11
fetching a *complete hierarchical tree* with multiple parents in one query and one result set without any iteration is not possible AFAIK. How should the result even look like? — Kaii, Jul 09 '12 at 21:16
http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter - here i wroted about how it should look like — Mister PHP, Jul 09 '12 at 21:20
you can help me in two ways, 1) to help me make a better query that will select 24 static levels of depth in an adjacency model list, 2) help me to render the query i have by now in php(using codeigniter), please help!!! — Mister PHP, Jul 09 '12 at 21:33

Kaii · Accepted Answer · 2012-07-20T19:49:09.257

4

You could iterate on the SQL side using MySQL query variables. This will return all childs with all data of one parent node without repeating yourself (and thus without imposing a limit on the depth of your tree)

something like this: (500 being the parents id to start with)

SELECT
  id,
  parent_id, 
  name, 
  '0' as depth, 
  @tree_ids := id AS foo
FROM 
   tree,
  (SELECT @tree_ids := '', @depth := -1) vars
WHERE id = 500
UNION
SELECT 
  id,
  parent_id,
  name,
  @depth := IF(parent_id = 500, 1, @depth + 1) AS depth,
  @tree_ids := CONCAT(id, ',', @tree_ids) AS foo
FROM 
  tree 
WHERE FIND_IN_SET(parent_id, @tree_ids) OR parent_id = 500

See a working example at SQLfiddle

Note that this gives a really bad performance on larger datasets because MySQL will not use your indexes and instead will do a full table scan. (i don't understand why its not using indexes, thats just how it is. if someone has advice on or explain the indexing issue, please comment!)

edited Jul 20 '12 at 19:49

answered Jul 09 '12 at 21:33

Kaii

20,122
3
38
60

it looks like mysql is not the best database for issues like this? – Mister PHP Jul 09 '12 at 21:52
Just out of interest i posted another question why it's not using INDEXes: http://stackoverflow.com/questions/11403768/select-with-query-variables-not-using-indexes – Kaii Jul 09 '12 at 21:54
i've used your solution, and i have a question, why the root parent is depth of 2, and the children is a depth of 1, copypasted your code and edited the ID to my – Mister PHP Jul 09 '12 at 22:19
maybe because the parent_id is not null, and it belongs to a more high level parent? – Mister PHP Jul 09 '12 at 22:24
This query actually is nuts when the IDs are not sequential. – Kaii Jul 09 '12 at 22:48
Wow its working))))))), but one question, why its returning BLOB - 0, BLOB - 5B etc...in the rows parent_id and name – Mister PHP Jul 09 '12 at 22:54
that happens in my phpmyadmin, so as i see it return non integer numbers – Mister PHP Jul 09 '12 at 23:04

score 2 · Answer 2 · answered Jul 09 '12 at 20:25

2

= comparisons work on only a single value. You can use in to compare against multiple values:

SELECT ...
FROM yourtable
WHERE somefield IN (select somevalue from othertable);

answered Jul 09 '12 at 20:25

Marc B

356,200
43
426
500

see this please http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter – Mister PHP Jul 09 '12 at 21:12
i think that this will be more complicated when i have 24 levels of depth....or can you show me on how to do it let's say on 5-10 levels of depth? – Mister PHP Jul 09 '12 at 21:31
For arbitrary nesting depths, you'd be better of switching to a DB that allows recursive queries, e.g. Postgres, and the "select by prior" stuff. You can fake it in MySQL with (going off memory) the 'adjacency set model', but I've never actually tried to deal with it. – Marc B Jul 09 '12 at 21:34
so it looks like Postgres is better than MySQL for this issues? – Mister PHP Jul 09 '12 at 21:56

score 0 · Answer 3 · edited May 23 '17 at 10:09

0

Storing heirarchical data in mysql and getting it out is not as simple as that.

Look into this: https://stackoverflow.com/a/4346009/9094

You will need more data to work with.

edited May 23 '17 at 10:09

Community

1
1

answered Jul 09 '12 at 20:25

databyss

6,318
1
20
24

http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter - see this please – Mister PHP Jul 09 '12 at 21:12

score 0 · Answer 4 · answered Jul 09 '12 at 20:27

0

It seems your DB relationship is setup to be MPTT, here is a good blog post exaplaining how to query mysql MPTT data http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Have a look at Full Tree example Retrieving a Full Tree in summary it can be done with joins.

answered Jul 09 '12 at 20:27

Abhishek Dujari

2,343
33
43

see this please http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter – Mister PHP Jul 09 '12 at 21:12
great! looks your problem was solved, I was totally in the wrong direction :) – Abhishek Dujari Jul 09 '12 at 21:27
i have readed that article and chosed the adjacent-list-model, but now i have a problem with rendering it in php, can you giму me a little example on how to do it? http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter - this is my question, please answer there – Mister PHP Jul 09 '12 at 21:28
no its not solved :(, those answers didn't helped me, one of those answers was to use sub-selects, thats why i am asking it here, before i was using the self-joins and i didn't render the tree in php, so please help, http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter - you can answer here – Mister PHP Jul 09 '12 at 21:30

score 0 · Answer 5 · answered Jul 09 '12 at 20:29

0

I am not 100% sure if I understood exactly what you mean, but if you want to select all columns separately from the table in a subselect...

col1, col2, col3, col4

you would need for each column a single subselect that always matches against the same WHERE. Example:

`SELECT * FROM main_table,
(SELECT col1 FROM inner_table WHERE inner_table.some_column=main_table.some_column),
(SELECT col2 FROM inner_table WHERE inner_table.some_column=main_table.some_column), ...`

answered Jul 09 '12 at 20:29

Chris

3,756
7
35
54

see this plaese http://stackoverflow.com/questions/11385250/how-render-a-tree-in-html-phpcodeigniter – Mister PHP Jul 09 '12 at 21:12
i don't know if its right make the best selection in this case? and it will be really good if you show me a little example on how to render it in php by making this sub-select query let's say on 5-10 levels(really i will have 24 static levels, it will not change) – Mister PHP Jul 09 '12 at 21:26

MySQL nested queries how to select more than one row

5 Answers5