SQL query for multiple levels in same table

Question

My issue is this. I am accessing a badly designed old equipment inventory system and what it has is multiple levels of equipment in a single table.

For Example:

Table: Equipment

|EQ_1 | EQ_2|
|123  |     |
|123  | 456 |
|123  | 567 |
|456  | 789 |
|567  |     |
|789  | 987 |

As you can see first piece of equipment has one entry for itself 123, and two further entries for pieces of equipment attached to it 456 and 567. From here 567 is only itself but 456 is attached to 789 and that is further attached to 987.

I have joined the table back onto itself a few times but I keep running into equipment with deeper connections than I compensate for. What I would like to find is a way to get the "endpoints" no matter how deep they are.

Example:

I input 123 as the piece of equipment I want to see all equipment on and I get a return of

I am doing this query through PHP code so if there are suggestions using that I am open to it but since there are hundreds of thousands of connections I would like to prevent passing the information back and forth from the Oracle servers to the Web server and making multiple queries. Pure SQL would be first choice, PHP/SQL mix second unless you can show it is more efficient.

Do you just want the distinct set of values that are in either eq_1 or eq_2? Given the result you want, it's not obvious to me that you need to work through the linkages at all. — Justin Cave
– Justin Cave, Commented Sep 14, 2015 at 17:43
The example I used has only a single device, the links need to be worked through because there are thousands of "start point" devices that are not linked to all values — James
– James, Commented Sep 14, 2015 at 18:04

James · Accepted Answer · 2015-09-14 18:35:11Z

0

From Marc B's comment on question I came across this answer to what I needed

SELECT DISTINCT EQ_1
                ,EQ_2
                ,LEVEL

FROM Equipment 
CONNECT BY PRIOR EQ_2 = EQ_1

answered Sep 14, 2015 at 18:35

James

491 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query for multiple levels in same table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related