CODEWITHSUNDEEP
sqloracle-databasejoin
proR
proR

Reputation: 39

Getting records from 2 tables with common an uncommon columns

Below is similar example of the issue I have:

if I have this table 1:

Patient ID Name Check in Date order name preformed by
1 Jack 12/sep/2002 xray Dr.Amal
2 Nora 15/oct/2002 ultrasound Dr.Goerge
1 Jack 13/nov/2003 Medicine Dr.Fred

table 2:

Patient ID Name Check in Date order name
1 Jack 14/Jun/2002 xray 2
2 Nora 15/oct/2002 ultrasound
1 Jack 13/nov/2003 Medicine
3 Rafael 13/nov/2003 Vaccine

The result I need is as the following:

Name Check in Date order name preformed by
Jack 12/sep/2002 xray Dr.Amal
Nora 15/oct/2002 ultrasound Dr.Goerge
Jack 13/nov/2003 Medicine Dr.Fred
Jack 14/Jun/2002 xray 2 Null
Rafael 13/nov/2003 Vaccine Null

If you noticed the result I need is all records of table 1 and all records of table 2 with no duplication and joining the same common fields and adding 'Preformed by' column from Table 1. I tried using 'UNION' as the following:

SELECT Name, Check_in_Date, order_name,preformed_by
  FROM table1 
UNION 
SELECT Name, Check_in_Date, order_name,''
  FROM table2

the result I get is 2 records for each patient with the same date one with preformed by one with null as the following:

Name Check in Date order name preformed by
Jack 12/sep/2002 xray Dr.Amal
Nora 15/oct/2002 ultrasound Dr.Goerge
Nora 15/oct/2002 ultrasound Null
Jack 13/nov/2003 Medicine Dr.Fred
Jack 13/nov/2003 Medicine null
Jack 14/Jun/2002 xray 2 Null
Rafael 13/nov/2003 Vaccine Null

If the same ID has same check in date in both table it must return the preformed by of table 1 not null How can I do this?

Thank you.

Upvotes: 1

Views: 157

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

What you need is a FULL JOIN matching by those three columns along with NVL() function in order to bring the values from table2 which return null from table1 such as

SELECT NVL(t1.name,t2.name) AS name,
       NVL(t1.check_in_date,t2.check_in_date) AS check_in_date,
       NVL(t1.order_name,t2.order_name) AS order_name,
       t1.preformed_by 
  FROM table1 t1
  FULL JOIN table2 t2
    ON t1.name = t2.name
   AND t1.check_in_date = t2.check_in_date
   AND t1.order_name = t2.order_name

or another method uses UNION to filter out duplicates and then applies an OUTER JOIN such as

SELECT tt.name, tt.check_in_date, tt.order_name, t1.preformed_by 
  FROM (
        SELECT name, check_in_date, order_name FROM table1 UNION
        SELECT name, check_in_date, order_name FROM table2
       ) tt
  LEFT JOIN table1 t1
    ON t1.name = tt.name
   AND t1.check_in_date = tt.check_in_date
   AND t1.order_name = tt.order_name

Demo

Upvotes: 4

Related Questions