Join tables with condition (Oracle)

Question

I need to join these 2 table to one table.

Table 1 (orderId, employeeId, name, arrival_time, departare_time, comment, GoodJob ,Reason, event_date)

Table 2 (orderId, GoodJob)

GoodJob is Yes/No field.

these are what I need to implement:

If there is a record in table 2, take the goodJob firstly from there. If not, take the value from table 1.
Table 1 feeds every day. I would like to have only the max event_date in the final table.
On the final table, when it's 'No' on GoodJob field, leave the Reason field as Null, if it 'yes' take the Reason.
The event_date field is only a date field (YYYY-MM-DD). I would like to have new field of creation_time. So, if there is 2 different recodes, one with 'No' and other with 'Yes' I could know what is the the most updated one.

Thanks.

Sample data and desired results would really help. As stated, for instance, you only want the maximum event date so that would be about one row. The fourth condition makes no sense. — Gordon Linoff
– Gordon Linoff, Commented Jun 20, 2020 at 22:18

Gordon Linoff · Accepted Answer · 2020-06-20 22:20:55Z

The first three conditions sounds like a join and window functions:

select t1.*, coalesce(t2.goodjob, t1.goodjob) as goodjob, (case when t2.goodjob is null then t1.reason end) as reason from (select t1.*, row_number() over (partition by ? order by event_date desc) as seqnum from table1 t1 ) t1 left join table2 t2 on t2.orderid = t1.orderid where seqnum = 1;

The ? is for whatever unit you want in the final result -- I doubt you want only one row. It might be orderid, but the question is unclear.

The fourth condition just doesn't make sense, because only one value is returned for the column you care about.

Collectives™ on Stack Overflow

Join tables with condition (Oracle)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related