David Cummings David Cummings - 4 months ago 5
MySQL Question

MYSQL Query issue with incorrect output

Hi all I have the following query :

$query = "SELECT DISTINCT registration, series, operator, msn, ln, model, status, pic, piccopy FROM (SELECT * FROM (SELECT * FROM history where status = 'active' ORDER BY date asc) AS a GROUP BY ln) a2 GROUP BY msn ORDER BY ln asc";


this works - Kind of.... see here

its currently listing all airframes that have been or currently are active. I need it to only list CURRENTLY active airframes. This is because an airframe is scrapped or put into a museum for example - its classed as no longer active - e.g. preserved, the same for those that may be scrapped, but the above query still sees it as active. What can I do to prevent this showing aircraft that may falling into one of the following categories:


  • grounded

  • scrapped

  • parted out

  • written off

  • missing

  • stored

  • preserved



however a grounded /stored/preserved airframe may be returned to service and become active again, so it needs to find the latest "active" and only display if its not been withdrawn after that date.

Im guessing its something simple where by it grabs the records for a certain airframe eg ET-AOP, and selects the last (latest by date aka newest date) sees if its active and if so displays it, if not then it wont. but how do I do this, its driving me mental

Answer

Since we don't have your table scheme, I've writing this as my best guess.

This is your history table:

history_id
plane_id 
status (active, grounded, missing etc.)

Selecting the planes with 'active' as latest status:

select plane_id from history 
 where history_id in (
  select max(history_id) from history group by plane_id
  )
 and status = 'active'