Join 2 tables with conditions (Oracle)

Hi guys

I need you help please with writing a SQL code.

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:

  1. If there is a record in table 2, take the goodJob firstly from there. If not, take the value from table 1.

  2. Table 1 feeds every day. I would like to have only the max event_date in the final table.

  3. On the final table, when it's 'No' on GoodJob field, leave the Reason field as Null, if it 'yes' take the Reason.

  4. 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.

please provide ddl and sample data. Also, looks like this is homework of some sort. At least provide us with something you've tried

hi

i am trying to do this !!

i am having doubts !! ..
instead of going back and forth several times ..
if i can remote desktop .. you and me together .. it will be done very very quickly

GoodJob is Yes/No field.

-- this is the join ..
select case when table2.goodJob in null then table1.goodJob
else table2.goodjob end
from table1 a join table2 b on a.orderid = b.orderid

If there is a record in table 2
-- my question ,( if there is record means ??? what !!!
does this mean NULL or value 'No' .. or no orderid in join )

take the goodJob firstly from there. If not, take the value from table 1.
-- my question ( what is this take the value ... is value (Yes/No ) or is value
some other column )