CODEWITHSUNDEEP
phpmysqlcodeigniter
Mister PHP
Mister PHP

Reputation: 327

MySQL nested queries how to select more than one row

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?

Upvotes: 0

Views: 3111

Answers (5)

Kaii
Kaii

Reputation: 20540

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!)

Upvotes: 4

Chris
Chris

Reputation: 3846

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), ...`

Upvotes: 0

Abhishek Dujari
Abhishek Dujari

Reputation: 2443

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.

Upvotes: 0

Marc B
Marc B

Reputation: 360592

= 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);

Upvotes: 2

databyss
databyss

Reputation: 6488

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.

Upvotes: 0

Related Questions