Master/Detail relationship in one query

Question

HI,

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]

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

Answer 1

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.

Answer 2

Try:

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

Answer 3

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