Master/Detail relationship in one query

I am not frequent SQL coder, I use it only for a simple tasks. I want to find a solution for a probably simple problem.

I have two tables:

Master_tbl [fields: ID, Plant, Detail_Group_ID]

and

Detail_tbl [fields: ID, Group_ID, Plant]

With such example data:

Master_tbl:

ID  Plant   Detail_Group_ID

1    Tree       1

2    Flower     2

Detail_tbl:

ID  Group_ID    Plant

1   1            Oak

2   1            Apple

3   1            Spruce

4   1            Maple

5   2            Tulip

6   2            Rose

7   2            Violet

8   2            Orchid

I want to make SQL statement to put two tables in one (in master/detail way):

The result table should display data in Treeview like fashion, where master fields are like parent nodes and detail fields like child nodes (see outcome开发者_开发知识库 below). I am not really sure, if this could be mastered via SQL...

Plant

Tree

Oak

Apple

Spruce

Maple

Flower

Tulip

Rose

Violet

Orchid

Thanx in advance.

SELECT Master_tbl.Plant as `Group`, Detail_tbl.Plant as Plant FROM Master_tbl
JOIN Detail_tbl on Master_tbl.Detail_Group_ID=Detail_tbl.Group_ID

That will give you output like:

Group   Plant
Tree    Oak
Tree    Apple
Tree    Spruce
Tree    Maple
Flower  Tulip
Flower  Rose
Flower  Violet
Flower  Orchid

Here's another option:

SELECT Master_tbl.Plant as `Group`, group_concat(Detail_tbl.Plant) as Plants 
FROM Master_tbl JOIN Detail_tbl on Master_tbl.Detail_Group_ID=Detail_tbl.Group_ID
group by `Group`;

The output is:

+--------+--------------------------+
| Group  | Plants                   |
+--------+--------------------------+
| Flower | Tulip,Rose,Violet,Orchid |
| Tree   | Oak,Apple,Spruce,Maple   |
+--------+--------------------------+

It's not necessarily the tree you wanted, but I think it's as close as you can get. In everything I could find, I couldn't find a way to get the tree output in one column like you posted. However, with these two queries, you can very easily parse the results based on the group.

Try this:

select lista.* from
(
SELECT Master_tbl.ID MasterID, 0 DetailID, Master_tbl.Plant FROM Master_tbl

UNION

SELECT Master_tbl.ID MasterID, Detail_tbl.Group_ID DetailID, Detail_tbl.Plant FROM Master_tbl
JOIN Detail_tbl on Master_tbl.Detail_Group_ID=Detail_tbl.Group_ID
) lista
order by MasterID, DetailID

You mean, like a JOIN ?

SELECT Master_tbl.ID as Master_ID, Master_tbl.Plant as Master_Plant, Detail_tbl.* FROM Master_tbl INNER JOIN Detail_tbl ON Master_tbl.Detail_Group_ID = Detail_tbl.Group_ID

Try:

SELECT d.Plant FROM Detail_tbl d INNER JOIN Master_tbl m ON
d.Group_ID = m.Detail_Group_ID