triplist.sql

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');