CODEWITHSUNDEEP
sqlsql-serversubquery
Nerdio
Nerdio

Reputation: 1

SQL query returns a 2nd result from a subquery

I have two tables

Table 1

ID Parent ID
00001256
00001298 00001256
00087612
00098542
00009214 00087612
00087343 00098542
00456336

Table 2

ID Flag
00001256 TRUE
10000000
00087612 TRUE
10000001
10000002
10000003
00456336 TRUE

I want to query Table 2 and find each row where Flag is TRUE. Then for each result, query Table 1 with the ID to find the row where Parent ID = ID. The query needs to return the ID found from Table 2, and any ID found from Table 1. The query on Table 1 might not return a row.

From the example above the expected result would be.

Results
00001256
00001298
00087612
00009214
00456336

I think I need to do this with a Subquery, but I don't understand how to return both results.

This is a very simple query on Table 2, which if my understanding is correct needs to be my sub query so it is executed first.

    SELECT ID
    FROM table2
    WHERE
    (
        Flag = TRUE
    )

I don't know how to 'wrap' this in another query to get the second result, if there is one, from Table 1

Any suggestions or guidance on how to write a suitable query would be appreciated.

Thanks

Upvotes: 0

Views: 49

Answers (1)

Dale K
Dale K

Reputation: 27388

A simple join between the 2 tables should return what you want

SELECT t1.Id
FROM Table2 t2
JOIN Table1 t1 ON t1.Id = t2.Id OR t1.Parent = t2.id
WHERE t2.Flag = 'TRUE';

Upvotes: 3

Related Questions