I have a situation where I need to join any common column (not provided to me) in table1 & table2 so that I can access column1 & column2 of table2 while joining both tables.
I need to find out common column(s) between table1 & table2 from dbc.tables or dbc.columns.
I tried the below but didn't get anything:
dbc.columns a, dbc.columns b
a.columnname = b.columnname
( (a.databasename = 'dbase1' and a.tablename = 'table1') AND (b.databasename = 'dbase2' and a.tablename = 'table2') );
in the second condition use b.tablename='table2' instead of a.tablename='table2'.
However it might not always be tru that the column names are the same across the tables. you should have the datamodel to see the linking between the tables.
Thanks. This realizes what a small displacement can do.
Thanks, Actually I am in a downstream data team that doesn't have access to the data model and other basic documents. So, the need to invent such query.