当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - MYSQL No duplicate Rows - Many-to-Many Hierarchy with Multiple Parents

问题描述:

I'm trying to create hierarchy result set. Sample records in the table.

In the below output I don't have parents name in the second column (root), likewise, I have 22 parents ( roots! without super parents).

Child Name Parent

-----------------

1 A 1-3

2 B 1-3

3 C 1-3

1-3 D 1-10

3-5 E 1-10

6-10 F 1-10

1-10 G 1-100

201 w 200-210

202 x 200-210

203 y 200-210

200-210 z 200-300

My Code: Here I have written the Mysql join query 3 times to get 4 levels.

SELECT

t1.Child ,

t1.Name ,

t2.Child ,

t2.Name ,

t3.Child ,

t3.Name,

t4.Child ,

t4.Name

FROM

code t1

left join code t2 on t1.Child = t2.Parent

left join code t3 on t2.Child = t3.Parent

left join code t4 on t3.Child = t4.Parent

Output:

t1.Child t1.Name t2.Child t2.Name t3.Child t3.Name t4.Child t4.Name

-------------------------------------------------------------------

1-100 root 1-10 G 1-3 D 1 A

1-100 root 1-10 G 1-3 D 1 B

1-100 root 1-10 G 1-3 D 1 C

1-10 G 1-3 D 1 A

1-10 G 1-3 D 1 B

1-10 G 1-3 D 1 C

1-3 D 1 A

1-3 D 1 B

1-3 D 1 C

200-300 root 200-210 z 201 w

200-300 root 200-210 z 202 x

200-300 root 200-210 z 203 y

200-210 z 201 w

200-210 z 202 x

200-210 z 203 y

I was just wanted line 1 to 3 & 10 - 12,

I'm looking for the rows like from

Grand Parents1(Root) to Grand Children's

Grand Parents2(Root) to Grand Children's

but not line 4 - 9 & 13 - 15 because the information is duplicated I have this information in already in col 3,4,5,6....etc

I don't want rows again to be duplicated

Excepted Output:

t1.Child t1.Name t2.Child t2.Name t3.Child t3.Name t4.Child t4.Name

-------------------------------------------------------------------

1-100 root 1-10 G 1-3 D 1 A

1-100 root 1-10 G 1-3 D 1 B

1-100 root 1-10 G 1-3 D 1 C

200-300 root 200-210 z 201 w

200-300 root 200-210 z 202 x

200-300 root 200-210 z 203 y

Thanks

网友答案:

You are on the right track. You can use exists in a where clause:

SELECT t1.Child, t1.Name, t2.Child, t2.Name,
       t3.Child, t3.Name, t4.Child, t4.Name
FROM code t1 LEFT JOIN
     code t2 on t1.Child = t2.Parent LEFT JOIN
     code t3 on t2.Child = t3.Parent LEFT JOIN
     code t4 on t3.Child = t4.Parent
WHERE NOT EXISTS (SELECT 1
                  FROM code c
                  WHERE c.Child = t1.Parent
                 );

This will return only the rows that start with a root.

In your case, you could also more simply do:

WHERE t1.Name = 'root'

If you prefer.

分享给朋友:
您可能感兴趣的文章:
随机阅读: