Select simplest element SQL Server recursion changed complexity of table

Question

I have T1 table with Id and name columns.

Table T2 with Aggregate_ID and Element_ID.

They are crossed

x1:

ID и Name

1 Car
2 Hood
3 Engine
4 Cylinder 
5 Wheel
6 tyre
7 rim (car)
8 Rim fixation (Car)

x2:

Aggregate_ID Element_ID

1 2
1 3 
1 4
1 5 
3 4
5 6
5 7
7 8

I need to select simplest element like 2, 4, 8

Complexity and number of elements can be varied.

How can I do it with recursion?

There is another task:

I need to output all the simplest elements of which consists Wheel.

Can you give an example, this isn't very clear. – Christian Phillips Oct 12 '13 at 08:35 — Christian Phillips, Oct 12 '13 at 08:35

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

Recursive solution in SQL can be very complex. In your case I see no need to use it, since it will only make your code more complex.

You can use CTE if you still insist: Recursive query in SQL Server

Non- recursive solution:,
You want only the elements that appear in T2 in the Element_ID but not in the Aggregate_ID:

SELECT Element_ID
FROM T2
EXCEPT
SELECT Aggregate_ID 
FROM T2

Or if you want to display all of the information for the elements:

SELECT *
FROM T1
WHERE T1.ID NOT IN (SELECT Aggregate_ID 
                    FROM T2)

edited May 23 '17 at 11:50

Community

answered Oct 12 '13 at 08:06

Yosi Dahari

Big thanks. My first step was CTE but I do not know how to do it. – streamc Oct 12 '13 at 09:01
I understood mechanism but I need more practice – streamc Oct 12 '13 at 09:06
You can remove all the `DISTINCT`, they are redundant. – ypercubeᵀᴹ Oct 12 '13 at 09:08
@ypercube - `DISTINCT` sometimes used like a hint in subqueries. 1/3 isn't redundant (the first one..) – Yosi Dahari Oct 12 '13 at 09:15
No, you are wrong. `EXCEPT` removes duplicates: **[SQL-Fiddle](http://sqlfiddle.com/#!3/41f94/2)** – ypercubeᵀᴹ Oct 12 '13 at 09:18
There is another task – streamc Oct 12 '13 at 09:29
I need to output all the simplest elements of which consists Wheel. – streamc Oct 12 '13 at 09:31
@ypercube -Thanks for the remark. I checked it and you are right. – Yosi Dahari Oct 12 '13 at 09:32

1 Answers1