oracle sql column alias

Question

I have 3 tables A, B, c and I want to join those tables. These tables have common columns like, id_no, order_no

and i want to write a query that returns all columns from all 3 tables with column name extension like tabA., tabB., tabC....I don't want to manually specify all column names. In that way i can differentiate the common columns among tables.

select tabA.id_no, tabA.order_no, tabA....., tabB.id_no, tabB.order_no,tabB..., tabC.id_no, tabC.order_no,tabC..
from A tabA, B tabB, C tabC
where tabA.id_no = tabB.id_no
and tabB.id_no = tabC.id_no

could u pls let me know how to achieve this in oracle sql.

Please post the CREATE TABLE statements. It's not clear from your description how the foreign keys are established. — The Impaler
– The Impaler, Commented Apr 23, 2018 at 21:38
If you're saying you want all columns to be prefixed by their table alias, there isn't a way to do that. — William Robertson
– William Robertson, Commented Apr 23, 2018 at 22:17
So, you want to have column names like id_no_tabA, id_no_tabB etc. in the result set? Sorry, but there is no easy way to avoid typing these name individually, by hand. If you have too many columns, and if you are creatively lazy (like me), you will write some code that will write the code for you; that's "meta-programming" though, there is no tool to do it directly. — user5683823
– user5683823, Commented Apr 23, 2018 at 23:00

thatjeffsmith · Accepted Answer · 2018-04-24 00:33:01Z

3

Oracle SQL Developer can do that.

Write your * query, put your mouse over the '*'

SQL Developer offers to explode that to the fully qualified column list, click the blue text.

Ta-da.

Don't forget your WHERE clause or ANSI join in the FROM, or your DBA will explain to you what a Cartesian product is.

If your table has foreign keys, SQLDev can generate that as well.

answered Apr 24, 2018 at 0:33

thatjeffsmith

22.7k6 gold badges44 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Faber · Accepted Answer · 2018-04-24 13:42:29Z

0

You can do the following:

SELECT tabA.*, tabB.*, tabC.*
  FROM a tabA INNER JOIN b tabB
    ON tabA.id_no = tabB.id_no
 INNER JOIN c tabC
    ON tabB.id_no = tabC.id_no;

EDIT

If you want only to get a list of the columns associated with the three tables, and to see which column names are common among the three, then you can try something like the following:

SELECT column_name, COUNT(*), LISTAGG(table_name, ',') WITHIN GROUP ( ORDER BY table_name
  FROM all_tab_columns
 WHERE owner = '<table_owner>'
   AND table_name IN ('A','B','C')
 GROUP BY column_name;

N.B. LISTAGG() assumes you're using Oracle 11g or greater; prior to that you can use the undocumented function WM_CONCAT().

Hope this helps.

edited Apr 24, 2018 at 13:42

answered Apr 23, 2018 at 21:35

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

2 Comments

user5683823 Over a year ago

This gives you no control over the column names in the result set, which seemed to be the only point of the OP's question.

David Faber Over a year ago

Perhaps I misunderstood what he or she was asking

Collectives™ on Stack Overflow

oracle sql column alias

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related