CODEWITHSUNDEEP
sqloracle-database
Randy
Randy

Reputation: 131

sql query to find record that contains IDs of other records in the same Oracle table

I need to run a SQL query against an Oracle 11 database that will retrieve records where one record itself has values under separate columns which hold the auto-generated ID(s) of one or more other records in the same table.

So, the records in the table would look something like this:

| FOREIGN_PARTY_ID | ORG_NAME | RELATED_PARTY_ID1 | RELATED_PARTY_ID2 | ...
| 1001             | null     | null              | null              | ...
| 1002             | null     | null              | null              | ...
| 1003             | null     | null              | null              | ...
| 1004             | null     | null              | null              | ...
| 1005             | ABC, INC. | 1001             | 1002              | 1003      | 1004

Upvotes: 1

Views: 447

Answers (1)

denied
denied

Reputation: 608

This query will return all rows in your table, which contains an auto-generated ID from FOREIGN_PARTY_ID in at least one RELATED_PARTY_IDn field:

SELECT
  t.*, 
  t1.*, /* actually, you can specify only fields you need */
  t2.*, 
  ...,
  tn.*
FROM YOUR_TABLE_NAME t
LEFT JOIN YOUR_TABLE_NAME t1 ON t1.RELATED_PARTY_ID1 = t.FOREIGN_PARTY_ID
LEFT JOIN YOUR_TABLE_NAME t2 ON t2.RELATED_PARTY_ID2 = t.FOREIGN_PARTY_ID
...
LEFT JOIN YOUR_TABLE_NAME tn ON tn.RELATED_PARTY_IDn = t.FOREIGN_PARTY_ID
WHERE
    t1.FOREIGN_PARTY_ID IS NOT NULL 
 OR t2.FOREIGN_PARTY_ID IS NOT NULL 
 OR ...
 OR tn.FOREIGN_PARTY_ID IS NOT NULL

If you want to get only the rows which contains your IDs in EVERY RELATED_PARTY_IDn field, you can change OR in the WHERE conditions by AND.

Upvotes: 1

Related Questions