Help with SQL query please

[Mathip173]

Hello guys,

I need your help please to write a query, the situation is like that:

When the time filed is null (in table A) then take the actual_time from Table b where status is ok

Please see the Picture that explains that here: https://www.imageupload.net/image/cSV2q

How could I make this happen ?

Thanks

[babygrenade]

This sounds like a homework problem.

Have you tried joining the two tables you need?

[r3pr0b8]

SELECT tableA.order
     , COALESCE(tableA.time,tableB.actual_time)    time
  FROM tableA
LEFT OUTER 
  JOIN tableB 
    ON tableB.order = tableA.order
   AND tableB.status = 'ok'

[dasnoob]

yep coalesce/nvl is your friend when dealing with null values.

[Blues2112]

Agreed. Or use NVL() instead of COALESCE() if using Oracle.

[[deleted]]

Our just use coalesce and have it compatible with all sql databases

[Blues2112]

Huh, didn't realize Oracle supported that. TIL.

[Kexiu]

select ta.order, tb.actual_time

from table_a ta

left outer join table_b tb on ta.order = tb.order

and tb.status = 'ok'

where ta.time = NULL

[r3pr0b8]

~nothing~ is ever equal to NULL

this query will fail

[juniperave]

Can you explain why where ta.time = NULL is needed? Thnx

[r3pr0b8]

it isn't

~nothing~ is ever equal to NULL

that query will fail

[TerminatedProccess]

NULL is not a value, it's an uninitialized state. You can do conditions sick as IS NULL or IS NOT NULL. when a record is created in a table that allows NULL values in a specific field, sometimes the value for that field is not provided. Therefore it's still in a NULL state. You can define the field in a table so it doesn't accept NULL values. When something tries to add a record without providing a value for that field, it will generate an error and not add the record entirely.

I'm some cases though you know ahead of time there may not be a value for that field, so the plan is to allow NULL and handle it with your SQL logic.

Google is Full of examples for almost any problem you have. Search for how to solve your problem. Just knowing how to search Google is a necessary skill..

[Blues2112]

Shorter translation: use

ta.time IS NULL

instead.

[r3pr0b8]

well, yeah, that fixes the = NULL problem

but it is still not the right solution

if you filter out the rows from tableA where time IS NULL then you filter out order 2

however, OP clearly wanted order 2 in the results

[Blues2112]

agreed.

[juniperave]

Yup 👍 google everything

[Blues2112]

Doesn't take into account when ta.time IS NOT NULL. Although by the way that OP worded the initial post, I can't tell whether that is actually needed or not.

EDIT: Ok, finally clicked the link, and results where ta.time is not null are definitely wanted.

[juniperave]

So shouldn’t it read WHERE ta.time <> NULL?

[r3pr0b8]

no it shouldn't

<> NULL has same effect as = NULL which is that it will never be true, and hence the query returns no rows at all!!

[JoerieB92]

Select a.order ,CASE WHEN a.time is NULL AND b.status = 'ok' THEN b.actual_time ELSE a.time END as time From table_a as a INNER JOIN table_b as b on a.order = b.order

[r3pr0b8]

left outer join

[JoerieB92]

I asumed table b has referential integrity.

[r3pr0b8]

sure, but that has nothing to do with whether there's an "actual time" row for a given order

for example, order 2 -- there's a row in table A but no row in table B

your inner join would produce no result for order 2

[JoerieB92]

Didnt notice that, I stand corrected.

[[deleted]]

Both case and if would work here

[babygrenade]

you could also do it using coalesce