user40987 user40987 - 1 month ago 5
SQL Question

How to show multiple rows associated with one ID if conditions in two fields are met

I have entries in a table that are associated with a single ID number:

ID TYPE ENTITY_ID Length(TRIM(Translate(entity_ID, ' +-.0123456789',' ')))
--------------------------------------------------------------------------------------
1111 Currency EURO 4
1111 Issuer 355555 (null)
2222 State WA 2
2222 Underwriter Foo Inc 7
2222 Obligor Alpha Assoc. 12


If the ID # has a type of either issuer or obligor AND the Entity_ID column of the associated row containing obligor or issuer IS NOT null, I want to show ALL of the rows associated with that ID number, not just the row/s that are not null.

This is my code for the above table so far:

Select ID, Type, Entity_Id, Length(TRIM(Translate(entity_ID, ' +-.0123456789',' ')))
from Meta_Detail where ID in( 1111, 2222)


With the conditions I stated above, both rows of 1111 would not show but all three rows of 2222 would. How can I accomplish this? I am using Oracle SQL.

Thanks

Answer
SELECT * FROM Meta_Detail WHERE ID IN (SELECT ID FROM Meta_Detail
WHERE (TYPE='Obligor' OR TYPE='Issuer' ) AND  ENTITY_ID IS NOT NULL)