CODEWITHSUNDEEP
sqloracle-database
JFS
JFS

Reputation: 3

How to merge and display columns of 2 tables?

hope someone can help! I have 2 tables:

**Table A**
Ref_No         1234
Shipment Loc   MNL
Shipment date  05/03
Shipment Qty   100

**Table B**
Ref_No        1234
Received Loc  MNL
Received date 06/03
Received Qty  90

How do I query and display all these columns in my table? The other 10 quantities is not showing up in Table B.

Rec Loc     MNL
Ship Date   05/03
Rec Date    06/03
Ship Qty    100
Rec Qty     90

Upvotes: 0

Views: 38

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

If I understood you correctly, that would be (outer?) join of these two tables:

SQL> alter session set nls_date_format ='dd.mm.yyyy';

Session altered.

SQL> with
  2  a (ref_no, shipment_loc, shipment_date, shipment_qty) as
  3    (select 1234, 'MNL', date '2020-03-05', 100 from dual),
  4  b (ref_no, received_loc, received_date, received_qty) as
  5    (Select 1234, 'MNL', date '2020-03-06', 90 from dual)
  6  select b.received_loc,
  7         a.shipment_date,
  8         b.received_date,
  9         a.shipment_qty,
 10         b.received_qty
 11  from a left join b on a.ref_no = b.ref_no;

REC SHIPMENT_D RECEIVED_D SHIPMENT_QTY RECEIVED_QTY
--- ---------- ---------- ------------ ------------
MNL 05.03.2020 06.03.2020          100           90

SQL>

Upvotes: 1

Related Questions