ziggy ziggy - 3 months ago 11
SQL Question

SQL "Where exists" with multiple tables with aliases

Example query:

Select id, id_dtm
From tableA
Where exists (
Select 1
From tableB b, tableC c, tableD d
Where b.id = id
And b.id_dtm = id_dtm
And b.id = c.id
And c.id = d.id);


The problem with the above query is that all 4 tables have columns named id and id_dtm.
When i run it, i get an error saying that the columns ORA-00918: column ambiguously defined

I could have fixed by using an alias in tableA but the problem is that the query is generated dynamically. The
where exists
portion is generated somewhere else and the bit before it is merged later so i cant use an alias as it is now.

Is there any way i can use id and id_dtm from tableA inside the
where exists
clause without using an alias for tableA?

Database is Oracle10G

Answer

Write the table name tableA:

Select id, id_dtm
From tableA 
Where exists (
 Select 1 
 From tableB b, tableC, tableD
 Where tableB.id = tableA.id
 And tableB.id_dtm = tableA.id_dtm
 And tableB.id = tableC.id
 And tableC.id = tableD.id)
Comments