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
– Alfabravo, Commented Jul 9, 2012 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
– Kaii, Commented Jul 9, 2012 at 20:47
stackoverflow.com/questions/11385250/… - here is the reason — Mister PHP
– Mister PHP, Commented Jul 9, 2012 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
– Kaii, Commented Jul 9, 2012 at 21:16
stackoverflow.com/questions/11385250/… - here i wroted about how it should look like — Mister PHP
– Mister PHP, Commented Jul 9, 2012 at 21:20

Kaii · Accepted Answer · 2012-07-20 19:49:09Z

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, 2012 at 19:49

answered Jul 9, 2012 at 21:33

Kaii

20.6k3 gold badges40 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mister PHP Over a year ago

it looks like mysql is not the best database for issues like this?

Kaii Over a year ago

Just out of interest i posted another question why it's not using INDEXes: stackoverflow.com/questions/11403768/…

Mister PHP Over a year ago

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 Over a year ago

maybe because the parent_id is not null, and it belongs to a more high level parent?

Kaii Over a year ago

This query actually is nuts when the IDs are not sequential.

|

Marc B · Accepted Answer · 2012-07-09 20:25:42Z

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 9, 2012 at 20:25

Marc B

362k44 gold badges433 silver badges508 bronze badges

4 Comments

Mister PHP Over a year ago

see this please stackoverflow.com/questions/11385250/…

Mister PHP Over a year ago

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?

Marc B Over a year ago

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.

Mister PHP Over a year ago

so it looks like Postgres is better than MySQL for this issues?

Community · Accepted Answer · 2017-05-23 10:09:26Z

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, 2017 at 10:09

CommunityBot

11 silver badge

answered Jul 9, 2012 at 20:25

databyss

6,5281 gold badge23 silver badges24 bronze badges

1 Comment

Mister PHP Over a year ago

stackoverflow.com/questions/11385250/… - see this please

Abhishek Dujari · Accepted Answer · 2012-07-09 20:27:16Z

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 9, 2012 at 20:27

Abhishek Dujari

2,44435 silver badges44 bronze badges

4 Comments

Mister PHP Over a year ago

see this please stackoverflow.com/questions/11385250/…

Abhishek Dujari Over a year ago

great! looks your problem was solved, I was totally in the wrong direction :)

Mister PHP Over a year ago

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? stackoverflow.com/questions/11385250/… - this is my question, please answer there

Mister PHP Over a year ago

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, stackoverflow.com/questions/11385250/… - you can answer here

Chris · Accepted Answer · 2012-07-09 20:29:16Z

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 9, 2012 at 20:29

Chris

3,8567 gold badges38 silver badges54 bronze badges

2 Comments

Mister PHP Over a year ago

see this plaese stackoverflow.com/questions/11385250/…

Mister PHP Over a year ago

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)

Collectives™ on Stack Overflow

MySQL nested queries how to select more than one row

5 Answers 5

7 Comments

4 Comments

1 Comment

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

4 Comments

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related