Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Problem with outer join

User_4LC0GJun 14 2021

Hello Experts,
Oracle DB version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production
Table Tracking

CREATE TABLE tracking (awb number(10) primary_key,dispatch_date date);

Sample Data

AWB          DISPATCH_DATE
1             20-JUN-2017
2             21-JUL-2017
3             31-JUL-2017
4             21-AUG-2017
5             21-APR-2020
6             21-MAY-2020

Table Tracking_item

CREATE TABLE tracking_item (item number(8),awb number(10));

Sample Data

IITEM      AWB    
1001        1
1001        4
1002        4
1002        6
1003        2
1004        5

Table Items

CREATE TABLE items (item number(8) primary_key, item_desc varchar2(100));

Sample Data

ITEM      ITEM_DESC
1001        Shirts
1002        Socks
1003        Trousers
1004        Belts
1005        T-Shirts
1006        Handkerchief

Requirement:
Pick items from items table for
If there is no tracking information present in the tracking table.
If tracking information is available then the dispatch_date (of all AWB's for that item) must be greater than 12 months. Even if one AWB is less than 12 months then dont pick it.

Desired Output

ITEM
1001
1003
1005
1006

My effort

select track_item.item
from (
       select ti.item item,
              max(tk.dispatch_date) dis_date
        from tracking tk, 
             tracking_item ti
       where tk.awb = ti.awb
       group by ti.item
     ) track_item
    ,items it
where months_between(sysdate,track_item.dispatch_date) > 12
and items.item = track_item.item(+);

Thank You

This post has been answered by Solomon Yakobson on Jun 14 2021
Jump to Answer

Comments

Post Details

Added on Jun 14 2021
8 comments
454 views