I have 2 tables tnHeaders and tnData
tnHeaders
| fnIDX | fnDESCRIPTION |
|---|---|
| 1 | h1 |
| 2 | h2 |
| 3 | h3 |
tnData
| fnIDX | fnHEADER_IDX | fnDESCRIPTION |
|---|---|---|
| 1 | 1 | d1 |
| 2 | 1 | d2 |
| 3 | 1 | d3 |
| 4 | 2 | d4 |
| 5 | 2 | d5 |
| 6 | 2 | d6 |
| 7 | 3 | d7 |
| 8 | 3 | d8 |
| 9 | 3 | d9 |
and would like to produce this output
| fnOUTPUT |
|---|
| h1 |
| d1 |
| d2 |
| d3 |
| h2 |
| d4 |
| d5 |
| d6 |
| h3 |
| d7 |
| d8 |
| d9 |
I can do this in code no problem, but how can I do this in SQL? (Make the server work)
CodePudding user response：
You need to use UNION ALL for the descriptions of both tables and sort the results in such a way that headers are on top of their data:
SELECT fnOUTPUT
FROM (
SELECT fnDESCRIPTION fnOUTPUT, fnIDX header_index, 1 is_header FROM tnHeaders
UNION ALL
SELECT fnDESCRIPTION, fnHEADER_IDX, 0 FROM tnData
) t
ORDER BY header_index, is_header DESC, fnOUTPUT;
See the demo.