0

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:

Person Package ID Date
1 Y788 01-01-2020
2 T932 01-02-2020
3 W345 01-03-2020
3 S122 01-03-2020
4 F567 01-04-2020
4 H767 01-04-2020

Table 2:

Person Package ID Date
1 Y788 01-01-2020
2 T932 01-02-2020
3 W345, S122 01-03-2020
4 F567, H767 01-04-2020
5 C555 01-05-2020

Desired Goal (after coding):

Person Package ID Date
5 C555 01-05-2020

Code:

SELECT Person, Package ID, Date
from table1, table2 
OUTER JOIN Person.table1 = Person.table2
Group by Date 
Order by Person;
mq19745422
  • 73
  • 5
  • 2
    no essential it is not there are other constrict like IN but you really should read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Sep 20 '22 at 16:39
  • 1
    Since you seem to want rows which doesn't exist in the other table, a `LEFT JOIN` would make most sense. You've got another issue though; you want to compare the separate values of person 4 with the concatenated values in another table. This is another good example why you shouldn't combine values in a row – HoneyBadger Sep 20 '22 at 17:16

1 Answers1

0

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

nbk
  • 45,398
  • 8
  • 30
  • 47