I have the following columns in a table : FDA_ID and FDA_ID_PARENT and the following set of data:
x
FDA_ID | FDA_ID_PARENT
771 | NULL
772 | 771
773 | 771
774 | 0
775 | NULL
776 | 775
777 | NULL
I have the FDA_ID = 773 as entry data and want to get those ‘linked’ to it.
What I mean by linked is those FDA_ID having the same FDA_ID as the FDA_ID_PARENT of FDA_ID = 773 and those having the same FDA_ID_PARENT as the FDA_ID_PARENT of FDA_ID = 773.
So in the case of FDA_ID = 773, the query would return FDA_ID = 771, 772 and 773.
I have tried the below query but it does not work:
select *
from FDA_TABLE FDA
where
FDA.FDA_ID = 773
or
FDA.FDA_ID in (select FDA_ID from FDA_TABLE where FDA_ID_PARENT = FDA.FDA_ID_PARENT);
Any help please?
Advertisement
Answer
The second part of your WHERE
clause returns all rows that have a parent set.
FDA.FDA_ID in (select FDA_ID from FDA_TABLE where FDA_ID_PARENT = FDA.FDA_ID_PARENT)
You may want to use something like (or any parts of it):
SELECT
*
FROM
FDA_TABLE
WHERE
-- self
FDA_ID = 773
-- parent
OR FDA_ID = (
SELECT FDA_ID_PARENT FROM FDA_TABLE WHERE FDA_ID = 773
)
-- siblings
OR FDA_ID_PARENT = (
SELECT FDA_ID_PARENT FROM FDA_TABLE WHERE FDA_ID = 773
)
-- children
OR FDA_ID_PARENT = 773