Getting different records in one table using one record from another table

[Al Hobbs]

Hello all,


I am stumped at the moment about how to write a certain oracle select query.

The flow is that I get the values from one row in table a, and then use different column values in that one row to search for many rows in another table.

What I have done so far is to use joins but it doesn't work because the  values I need from the 'another' table use the same column.

select     cm.CD_NM as VALUE1, f.CD_NM as VALUE2, tm.CD_NM as VALUE3, ast.CD_NM as VALUE4, em.CD_NM as VALUE5 from     TABLE1 c left join TABLE2 cm on c.COLUMN03 = cm.CODE and CD_GRP = 'COLUMN03' left join TABLE2 f on c.COLUMN05 = f.CODE and CD_GRP = 'COLUMN05' left join TABLE2 tm on c.COLUMN07 = tm.CODE and CD_GRP = 'COLUMN07' left join TABLE2 ast on c.COLUMN04 = ast.CODE and CD_GRP = 'COLUMN04' left join TABLE2 em on c.COLUMN02 = em.CODE and CD_GRP = 'COLUMN02' where MAIN_CODE = 'ABCD'    


Doing it as so gives a 'ORA-00918 column ambiguously defined' error.


select     cm.CD_NM as VALUE1, f.VALUE2 as VALUE2 from     table1 c left join table2 cm on c.COLUMN03 = cm.CODE and CD_GRP = 'COLUMN03' left join (select CD_NM as VALUE2, CODE as CODE2 from table2 where CD_GRP = 'COLUMN05') f on c.COLUMN05 = f.CODE2 where MAIN_CODE = 'ABC'              
I was able to get it working by creating sub queries for the left joins.  
Is there a better way?

[Tim Holloway]

Yes. The "AS" clauses allow you to define shorthand IDs for tables (and views), so you can unambiguously reference fields from different sources in the same SELECT. It's not uncommon to have a JOIN clause that looks like "ON c.user_id = f.user_id".

[Al Hobbs]

The SQLI posted has aliases for the tables.
When there is one join it works without problems, the problem is when there is more than one and selecting the same column.

Right now the only way to make it not throw that error is to have a subquery with column aliases after the first left join.


if there is a way to give an alias to the column within the join statement that might solve it.

Is that possible to do?

After looking at the original statement again. The problem was that the join statements didn't have the table alias in front of one columns.
It only became a problem after adding more joins so I assumed it was due to using the same column name.

[Tim Holloway]

Generally the "real" (fully-qualified) name of a column is going to be something like "databasename.schemaname.tablename.columnname" (Not all databases use quite that structure, but many do). So if you just say "columnname" then the upper levels are assumed from context.

Two column names are not the "same" column if their fully-qualified names are different. So in cases of ambiguity you just need to add better qualifications.

[Al Hobbs]

Yes, that sounds right.
The problem was the I didn't put the table name in front of CD_GRP

select        cm.CD_NM as VALUE1,    f.CD_NM as VALUE2,    tm.CD_NM as VALUE3,    ast.CD_NM as VALUE4,    em.CD_NM as VALUE5 from        TABLE1 c left join        TABLE2 cm    on c.COLUMN03 = cm.CODE        and cm.CD_GRP = 'COLUMN03' left join        TABLE2 f    on c.COLUMN05 = f.CODE        and f.CD_GRP = 'COLUMN05' left join        TABLE2 tm    on c.COLUMN07 = tm.CODE        and tm.CD_GRP = 'COLUMN07' left join        TABLE2 ast    on c.COLUMN04 = ast.CODE        and ast.CD_GRP = 'COLUMN04' left join        TABLE2 em    on c.COLUMN02 = em.CODE        and em.CD_GRP = 'COLUMN02' where    MAIN_CODE = 'ABCD'  

Thank you!