I am trying to find the difference of ID from 2 tables. I have posted the sample data of Table 1 and Table 2. I was thinking of using OUTER JOIN, but realized that may not be the best idea. What is a simple way to find the desired goal using JOIN? Would any JOIN clause help in this case? If not, what is the best (yet simple) approach to this? I appreciate everyone's help with this!

Table 1:

Table 2:

Desired Goal (after coding):

Code:

SELECT Person, Package ID, Date
from table1, table2 
OUTER JOIN Person.table1 = Person.table2
Group by Date 
Order by Person;

CodePudding user response：

In my comment i already mentioned that saving data in delimited column is a bad idea, as it complicates matters and makes the query slow see Is storing a delimited list in a database column really that bad?

The rest is basic sql

A LEFT JOIN is needed to get all rows in table2 that are not in table1.

The where clause only works, because there are no spaces between the data in table2, so if you have them you need to replace them.

SELECT table2."Person", table2."Package ID", table2."Date"  
FROM table2 LEFT JOIN table1 ON  table1."Person" = table2."Person"
WHERE  (',' || table2."Package ID" || ',') NOT LIKE ('%,' || COALESCE(table1."Package ID",'') || ',%')

| Person | Package ID | Date |
| :-------|:-----------|:-----|
| 5 | C555 | 01-05-2020 |

fiddle