Selecting output from two DB Tables to one output

Question

I have two tables, operators (who operates an aircraft) and history (history is the movements of an aircraft). What I need to do is only show the operators which have an "active" flag on the history table as the last entry. For instance, I have in the history table 10 entries for an aircraft in the history table. the last entry for this is for British Airways and the aircraft is active. this should, therefore, should allow British Airways to be listed on the output. I then have 10 entries for an aircraft which was with Fly Uk, then has been sold to British Airways. Both had or have the aircraft as active, but as British Airways is the current active operator for the aircraft it shouldn't show FLY UK in this list. Both Operators may have multiple aircraft in the list, some as active and some as on order, stored, etc. tables -

History has a field for operator and status (where this can be active, stored, scrapped, written off)

Aircraft Has a field for the OperatorName

so it should be something like

select * 
from operators 
where max history.status = 'active' or 'stored' or 'grounded';

however it's not working?

I have tried various things with the code, changed php version and no better in the situation

select * 
from operators 
where max history.status = 'active' or 'stored' or 'grounded';

I don't get any output with this

Answer 1

You can use a correlated subquery:

select o.*
from operators o
where (
    select h.status
    from history h
    where h.operatorName = o.operatorName
    order by h.operationDate desc
    limit 1
) in ('active', 'stored', 'grounded')

In MySQL 8.0, window functions come handy:

select *
from (
    select 
        o.*, 
        h.status,
        rank() over(partition by h.operatorName order by h.operationDate desc) rn
    from operators
    inner join history h on h.operatorName = o.operatorName
) t
where 
    rn = 1 
    and status in ('active', 'stored', 'grounded')

---

history.status = 'active' or 'stored' or 'grounded' won't work as you expect. This will always be true.

There is in fact 3 conditional statements :
history.status = 'active'
'stored'
'grounded'

'stored' and 'grounded' will never be false, because a non empty string is not considered false

Your condition can be rewritten this way, and the logic is kept : (history.status = 'active') or (TRUE) or (TRUE) that evaluates to TRUE no matter the value of history.status