Skip to content

Instantly share code, notes, and snippets.

@stwalkerster
Created Dec 19, 2016
Embed
What would you like to do?
select
date_format(
date_add(t.departure, INTERVAL 1286 YEAR),
'%D %b %Y'
) as departure
, concat(coalesce(concat(t.departurestation, ',<br />'),''), sysfrom.name) as departure_location
, group_concat(sysv.name separator ', ') as via
, concat(coalesce(concat(t.arrivalstation, ',<br />'),''), systo.name) as arrival_location
, date_format(
date_add(t.arrival, INTERVAL 1286 YEAR),
'%D %b %Y'
) as arrival
, t.linkpage
, t.linktag
from stwalkerster_ed_explore.trip t
inner join stwalkerster_ed_explore.waypoint wto on wto.id = t.to and wto.special = 'a'
inner join stwalkerster_ed_explore.system systo on wto.system = systo.id
inner join stwalkerster_ed_explore.system sysfrom on sysfrom.id = t.from
left join stwalkerster_ed_explore.waypoint v on v.trip = t.id and v.special = 'v'
left join stwalkerster_ed_explore.system sysv on v.system = sysv.id
group by t.departure, sysfrom.name, systo.name, t.arrival, t.linkpage, t.linktag
order by coalesce(t.departure, '9999-12-31');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment