SQL - Use results of one query as search criteria for another query

Question

I have a table structure which looks similar to example below. Table1

Component             Item

Screen                Monitor
Monitor               LED
Monitor               LCD            
Monitor               CRT
Battery               Vehicle
Vehicle               Car
Car                   Green_Car
Car                   Red_Car
Red_Car               A_Red_Car
Red_Car               B_Red_Car
Red_Car               C_Red_Car
Car                   Blue_Car
Blue_Car              X_Blue_Car
Vehicle               Van
Van                   4D_Van
Van                   6D_Van
6D_Van                White_6D_Van
6D_Van                Black_6D_Van
Van                   8D_Van
Vehicle               Bus
Bus                   HR_Bus
HR_Bus                XL_HR_Bus
HR_Bus                XXL_HR_Bus
Bus                   LR_Bus
LR_Bus                White_LR_Bus
Vehicle               Train
Train                 S_Train
Train                 L_Train
Train                 XL_Train
XL_Train              F_XL_Train
Battery               Toy
Toy                   Doll
Toy                   Robot
Robot                 Talking_Robot
Robot                 Walking_Robot
Robot                 FO_Robot
FO_Robot              Red_FO_Robot
Red_FO_Robot          M_Red_FO_Robot
Red_FO_Robot          XXL_Red_FO_Robot
FO_Robot              Yellow_FO_Robot
Yellow_FO_Robot       M_Yellow_FO_Robot
Yellow_FO_Robot       XL_Yellow_FO_Robot
Toy                   Teddy Bear
Teddy Bear            Talking_Teddy Bear
Teddy Bear            Walking_Teddy Bear
Battery               Electrical Item
Electrical Item       Radio
Electrical Item       Remote Controller
Remote Controller     TV
Remote Controller     Audio
Remote Controller     Box
Electrical Item       Fan
Box                   Shoes
Box                   Computer

As seen there are multiple levels of record in this table. What I want to view is everything that has a battery as a component.

SELECT * FROM Table1 WHERE Component = 'Battery'

will give me everything that has 'Battery' directly as a Component. What I want is everything that's using a battery(Directly and indirectly) so all sub categories of the result set of above query basically.

For example, a Robot has a battery. also a XXL_Red_FO_Robot has a battery, so has All Yellow_FO_Robots but isn't directly visible in table.

Could someone advise please as to how to get this?

Please edit your table, structure is completely unreadable – HoneyBadger Jan 13 '16 at 09:02 — HoneyBadger, Jan 13 '16 at 09:02

HoneyBadger · Answer 1 · 2016-01-13T10:07:32.937

Perhaps this works for you, if I understand you correctly:

SELECT  * 
FROM    Table1 T1
WHERE   Component = 'Battery' 
    OR  EXISTS (
                    SELECT  NULL 
                    FROM    Table1 T
                    WHERE   Component = 'Battery'
                       AND  T.Item = T1.Component
               )

Edit

In order to search multiple levels try this:

DECLARE @Table1 TABLE (Component VARCHAR(255), Item VARCHAR(255))

INSERT @Table1
        ( Component, Item )
VALUES  ('Screen','Monitor')
,       ('Monitor','LED')
,       ('Monitor','LCD')
,       ('Monitor','CRT')
,       ('Battery','Vehicle')
,       ('Vehicle','Car')
,       ('Car','Green_Car')
,       ('Car','Red_Car')
,       ('Red_Car','A_Red_Car')
,       ('Red_Car','B_Red_Car')
,       ('Red_Car','C_Red_Car')
,       ('Car','Blue_Car')
,       ('Blue_Car','X_Blue_Car')
,       ('Vehicle','Van')
,       ('Van','4D_Van')
,       ('Van','6D_Van')
,       ('6D_Van','White_6D_Van')
,       ('6D_Van','Black_6D_Van')
,       ('Van','8D_Van')
,       ('Vehicle','Bus')
,       ('Bus','HR_Bus')
,       ('HR_Bus','XL_HR_Bus')
,       ('HR_Bus','XXL_HR_Bus')
,       ('Bus','LR_Bus')
,       ('LR_Bus','White_LR_Bus')
,       ('Vehicle','Train')
,       ('Train','S_Train')
,       ('Train','L_Train')
,       ('Train','XL_Train')
,       ('XL_Train','F_XL_Train')
,       ('Battery','Toy')
,       ('Toy','Doll')
,       ('Toy','Robot')
,       ('Robot','Talking_Robot')
,       ('Robot','Walking_Robot')
,       ('Robot','FO_Robot')
,       ('FO_Robot','Red_FO_Robot')
,       ('Red_FO_Robot','M_Red_FO_Robot')
,       ('Red_FO_Robot','XXL_Red_FO_Robot')
,       ('FO_Robot','Yellow_FO_Robot')
,       ('Yellow_FO_Robot','M_Yellow_FO_Robot')
,       ('Yellow_FO_Robot','XL_Yellow_FO_Robot')
,       ('Toy','TeddyBear')
,       ('TeddyBear','Talking_TeddyBear')
,       ('TeddyBear','Walking_TeddyBear')
,       ('Battery','ElectricalItem')
,       ('ElectricalItem','Radio')
,       ('ElectricalItem','RemoteController')
,       ('RemoteController','TV')
,       ('RemoteController','Audio')
,       ('RemoteController','Box')
,       ('ElectricalItem','Fan')
,       ('Box','Shoes')
,       ('Box','Computer')
;

WITH CTE AS
(
        SELECT      T.Component
        ,           T.Item
        FROM        @Table1 AS T
        WHERE       T.Component = 'Battery'

        UNION ALL

        SELECT      T.Component
        ,           T.Item
        FROM        @Table1 AS T
        INNER JOIN  CTE
                ON  T.Component = CTE.Item
)

SELECT * FROM CTE

Results:

Component   Item
Battery Vehicle
Battery Toy
Battery ElectricalItem
ElectricalItem  Radio
ElectricalItem  RemoteController
ElectricalItem  Fan
RemoteController    TV
RemoteController    Audio
RemoteController    Box
Box Shoes
Box Computer
Toy Doll
Toy Robot
Toy TeddyBear
TeddyBear   Talking_TeddyBear
TeddyBear   Walking_TeddyBear
Robot   Talking_Robot
Robot   Walking_Robot
Robot   FO_Robot
FO_Robot    Red_FO_Robot
FO_Robot    Yellow_FO_Robot
Yellow_FO_Robot M_Yellow_FO_Robot
Yellow_FO_Robot XL_Yellow_FO_Robot
Red_FO_Robot    M_Red_FO_Robot
Red_FO_Robot    XXL_Red_FO_Robot
Vehicle Car
Vehicle Van
Vehicle Bus
Vehicle Train
Train   S_Train
Train   L_Train
Train   XL_Train
XL_Train    F_XL_Train
Bus HR_Bus
Bus LR_Bus
LR_Bus  White_LR_Bus
HR_Bus  XL_HR_Bus
HR_Bus  XXL_HR_Bus
Van 4D_Van
Van 6D_Van
Van 8D_Van
6D_Van  White_6D_Van
6D_Van  Black_6D_Van
Car Green_Car
Car Red_Car
Car Blue_Car
Blue_Car    X_Blue_Car
Red_Car A_Red_Car
Red_Car B_Red_Car
Red_Car C_Red_Car

This solution would only allow solutions up to depth two. But what about `(Vehicle,Train)` `(Train,XL_Train)` `(XL_Train, F_XL_Train)`. — CAFEBABE, Jan 13 '16 at 09:36
@CAFEBABE, that's true, hadn't thought of multiple levels. For more depth a recursive CTE is probably warranted... — HoneyBadger, Jan 13 '16 at 09:38

score 0 · Answer 2 · edited May 23 '17 at 12:15

(As already somebody wrote in the comments your table is hardly readable)

If I understand you correctly your goal is to calculate the transitive closure of a relation. This is. all b such that r(a,b). But also all b s.t. where exists c such that r(a,c),r(c,b),.....

This is not solvable in standard SQL.

However, this thread might help you for sql server. How can I create a closure table using data from an adjacency list?

(and for postgres Recursive query used for transitive closure)

score 0 · Answer 3 · answered Jan 13 '16 at 11:00

You can see hierarchy of any component Battery, Robot etc.

WITH MyCTE
AS 
( 
    SELECT Component, Item
    FROM Table1
    WHERE Component = 'Robot'

    UNION ALL

    SELECT Table1.Component, Table1.Item
    FROM Table1
    INNER JOIN MyCTE ON Table1.Component = MyCTE.Item
    WHERE Table1.Component <> 'Robot'
)
SELECT * FROM MyCTE

SQL - Use results of one query as search criteria for another query

3 Answers3

Linked