Selecting output from two DB Tables to one output

100
October 31, 2019, at 08:00 AM

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

Rent Charter Buses Company
READ ALSO
MySQL error I can't understand, grademax error [duplicate]

MySQL error I can't understand, grademax error [duplicate]

This question already has an answer here:

103
insert facebook leads into mysql database php [on hold]

insert facebook leads into mysql database php [on hold]

i have to insert Facebook leads into MySQL database using PHP but i did not find any proper wayif you have any idea or any link from where i can take help please let me know

78
Xamp v3.2.4 mysql isn't working. Table doesn't exist in engine [on hold]

Xamp v3.2.4 mysql isn't working. Table doesn't exist in engine [on hold]

I tried each and every answer of stack overflow but it didn't help me

129
Como inserir um filtro no código sql? [on hold]

Como inserir um filtro no código sql? [on hold]

Estou configurando o código sql abaixo e não consigo colocar um filtro de data nele

114