CODEWITHSUNDEEP
sqloracle-databasejoin
mavili
mavili

Reputation: 3424

SQL: Join/union of two tables, on multiple conditions

Provided the following structure and data:

CREATE TABLE "CHANGES" (    
  "ID" NUMBER(38),
  "LAST_UPD_DATE" DATE DEFAULT SYSDATE
);

CREATE TABLE "EXPORT_LOG" (
  "ID" NUMBER(38),
  "LAST_EXPORT" DATE DEFAULT SYSDATE
);

Table CHANGES contains:

----------------------------
| ID  |  LAST_UPD_DATE     |
----------------------------
| 123 | 12-MAY-16 12.23.23 |
| 124 | 12-MAY-16 12.24.23 |
| 125 | 12-MAY-16 12.11.23 |
----------------------------

and EXPORT_LOG

----------------------------
| ID  |  LAST_EXPORT       |
----------------------------
| 124 | 12-MAY-16 12.23.12 |
| 125 | 12-MAY-16 12.12.24 |
----------------------------

I need to get the records in CHANGES that either don't exist in EXPORT_LOG or, if exists, get records with LAST_UPD_DATE later than LAST_EXPORT. So in the above example, I should be getting 123 and 124.

I'm trying different JOINs but I cannot seem to get the result I want: INNER JOIN is used for intersections, LEFT JOIN gets ALL of first table, but only those of second table that match a condition you set - none of these is what I want. So is the solution some sort of UNION?

Upvotes: 0

Views: 1418

Answers (3)

Navin
Navin

Reputation: 1

Please use the below mentioned code and provide me the feedback. It should work as per your requirement.

SELECT CH.ID, CH.LAST_UPD_DATE
FROM CHANGES CH, EXPORT_LOG EL 
WHERE CH.ID = EL.ID(+) 
AND ((EL.ID IS NULL) OR (CH.LAST_UPD_DATE > EL.LAST_EXPORT));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269783

One method is to translate the conditions directly using exists:

select c.*
from changes c
where not exists (select 1 from export_log el where c.id = el.id) or
      not exists (select 1 from export_log el where c.id = el.id and el.last_export > c.last_upd_date);

This can be simplified to:

select c.*
from changes c
where not exists (select 1 from export_log el where c.id = el.id and el.last_export > c.last_upd_date);

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT t1.*
FROM CHANGES AS t1
LEFT JOIN EXPORT_LOG AS t2 ON t1.ID = t2.ID 
WHERE (t2.ID IS NULL) OR (t1.LAST_UPD_DATE > t2.LAST_EXPORT)

This will return all records of CHANGES table that don't have a match in EXPORT_LOG table plus the records of CHANGES table that have a LAST_UPD_DATE that is later than LAST_EXPORT.

Upvotes: 2

Related Questions