CODEWITHSUNDEEP
sqloracle-database
tonyf
tonyf

Reputation: 35577

Join two tables and return a column value multiple times in Oracle

I have two tables - TABLE_A and TABLE_B.

TABLE_A 

AREA_ID  LOC_ID   OTHER
------   -------- -----
111      1        AA
222      2        BB
333      3        CC

TABLE_B 
-------

LOC_ID   LOC_NAME 
-------  ----------
1        USA
2        ITALY
3        SPAIN

Based on the above, I would like to query all the records in TABLE_A and join on TABLE_B using LOC_ID but return USA from TABLE_B for all AREA_IDs in TABLE_A.

Result I am after is:

Result:

AREA_ID  NAME
------   --------
111      USA
222      USA
333      USA

That is, I would like to repeat the LOC_NAME in TABLE_B for LOC_ID = 1 (USA) and assign this name to all AREA_ID records in TABLE_A.

Upvotes: 0

Views: 519

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

Use cross join as

select a.area_id, b.loc_name as name
  from table_a a
 cross join table_b b 
 where b.loc_id = 1

or inner join as 

select a.area_id, b.loc_name as name
  from table_a a
  join table_b b 
    on b.loc_id = 1

Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This seems to do what you want:

select a.area_id, 'USA' as loc_name
from table_A a;

I can't figure out why a join to table_B is desired.

If you really want the value with "1", then use:

select a.area_id, b.loc_name
from table_A a join
     table_B b
     on b.loc_id = 1;

You might want a left join . . . but then you would get a NULL value.

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 143023

Something like this?

If you change condition in line #15, you'll get another LOC_NAME from table_B.

SQL> with table_a (area_id, loc_id, other) as
  2    (select 111, 1, 'AA' from dual union all
  3     select 222, 2, 'BB' from dual union all
  4     select 333, 3, 'CC' from dual
  5    ),
  6  table_b (loc_id, loc_name) as
  7    (select 1, 'USA'   from dual union all
  8     select 2, 'ITALY' from dual union all
  9     select 3, 'SPAIN' from dual
 10    )
 11  select a.area_id, x.loc_name
 12  from table_a a join
 13       (select b.loc_name
 14        from table_b b
 15        where b.loc_id = 1       --> that's what you said
 16       ) x
 17       on 1 = 1;

   AREA_ID LOC_N
---------- -----
       111 USA
       222 USA
       333 USA

SQL>

Upvotes: 0

Related Questions