Skip to content

Instantly share code, notes, and snippets.

@stwalkerster
Created December 19, 2016 13:11
Show Gist options
  • Save stwalkerster/71e68eb0dd9740beee2449d6d8e519ae to your computer and use it in GitHub Desktop.
Save stwalkerster/71e68eb0dd9740beee2449d6d8e519ae to your computer and use it in GitHub Desktop.
select
1 as ordergroup
, 0 as ordergroup2
, 'Departure' as stage
, concat(coalesce(concat(t.departurestation, ', '),''), s.name) as system
, null as source
, 0 as reached
from stwalkerster_ed_explore.trip t
inner join stwalkerster_ed_explore.system s on s.id = t.`from`
where t.id = %d
union all
select
3 as ordergroup
, 0 as ordergroup2
, 'Arrival' as stage
, concat(coalesce(concat(t.arrivalstation, ', '),''), s.name) as system
, null as source
, w.reached as reached
from stwalkerster_ed_explore.trip t
inner join stwalkerster_ed_explore.waypoint w on w.id = t.to and w.special = 'a'
inner join stwalkerster_ed_explore.system s on s.id = w.system
where t.id = %d
union all
select
2 as ordergroup
, w.sequence as ordergroup2
, case w.special
when 'v' then concat('Via ', w.number)
else concat('Waypoint ', w.number)
end as stage
, coalesce(s.name, '<i>Unplotted</i>') as system
, concat('CMDR ', c.name) as source
, w.reached as reached
from stwalkerster_ed_explore.waypoint w
left join stwalkerster_ed_explore.system s on s.id = w.system
left join stwalkerster_ed_explore.commander c on c.id = w.commander
where w.trip = %d
and coalesce(w.special, 'v') = 'v'
order by ordergroup, ordergroup2;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment