Get column values as rows, recursively from a hierarchy in database

Question

I have a query that returns me this:

+-------------+-------------+-------------+-------------+
| L_1_teacher | L_1_student | L_2_teacher | L_2_student |
+-------------+-------------+-------------+-------------+
|      333333 |       33667 |      111111 |       33668 |
|      222222 |       33667 |      111111 |       33669 |
|      222222 |       33667 |      111111 |       33670 |
+-------------+-------------+-------------+-------------+

I need to "union" the two teacher columns. The expected output is:

+-------------+
|    teachers |
+-------------+
|      333333 |
|      222222 |
|      111111 |
+-------------+

Since it's a long query, I cannot afford to run it twice (actually, I have more than two levels of teachers), select teachers on every level and union them.

What I tried: I stored the results of this query into a temp table tree and did:

SELECT L_1_teacher as "teachers" FROM tree
UNION
SELECT L_2_teacher as "teachers" FROM tree

but it threw me an error saying Error Code: 1137. Can't reopen table: 'tree' (MySQL prohibits using the same temporary table twice in the query)

How can I accomplish this?

This is called a PIVOT query. Unfortunately, mysql does not have a native solution for pivoting. There are however [many examples](http://stackoverflow.com/search?q=mysql+pivot) of pivot style queries in mysql. The union that you mention is one of them. — crthompson, Apr 07 '15 at 16:48
@paqogomez Thanks for the pointer. I checked the link out. There's a haystack of questions. I was wondering if you could suggest a solution here, if that wouldn't be too much to ask for :) This is kinda urgent. Hence the request. — th3an0maly, Apr 07 '15 at 16:50
Happy to help, but as @AbhikChakraborty suggests, we need a bit more info. Could you put together a sample [sql fiddle](http://sqlfiddle.com)? Or let us understand what about this query is so intensive? — crthompson, Apr 07 '15 at 16:52
@paqogomez, AbhikChakraborty Here's a fiddle http://sqlfiddle.com/#!9/5c3cb/2 — th3an0maly, Apr 07 '15 at 17:26
I think what you have here is an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) You've asked about how to pivot this query, when your real question should be about how to do [recursion](http://stackoverflow.com/a/24901882/2589202) or [parent child queries](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) in mysql. — crthompson, Apr 07 '15 at 17:58

score 3 · Accepted Answer · answered Apr 07 '15 at 16:53

3

I confirm your tought, you can't refer twice in the same query to a temporary table. This is already documented in MySQL doc.

An alternative would be to create another temporary table.

CREATE TEMPORARY TABLE tree2 LIKE tree;
INSERT INTO tree2 select * from tree;

SELECT L_1_teacher as "teachers" FROM tree
UNION
SELECT L_2_teacher as "teachers" FROM tree2

answered Apr 07 '15 at 16:53

Jean-François Savard

20,626
7
49
76

I thought about this. But did not seem like an elegant solution. – th3an0maly Apr 07 '15 at 16:56
I'm curious why a downvote... @th3an0maly Unfortunately it seems like it is the only solution, with MySQL. – Jean-François Savard Apr 07 '15 at 16:57
I was the downvoter and perhaps its too harsh. You have answered the question as it was asked. However, I think this is going to be an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). We just dont have enough information to give a good answer. – crthompson Apr 07 '15 at 16:59
@paqogomez I also think this seems to be an XY problem like most of the temporary table related question. However, creating another table is AFAIK the only solution if really you can't avoid the usage of union on the tmp table. In any ways, thanks for removing the downvote. – Jean-François Savard Apr 07 '15 at 17:01
As i suspected, its not a pivot or a temp table problem, its a tree problem. – crthompson Apr 07 '15 at 17:51

Get column values as rows, recursively from a hierarchy in database

1 Answers1