session-info.sql

SELECT
  s.jumpsmade,
  s.madness,
  sys.name  AS current_system,
  sys.x as curr_x,
  sys.y as curr_y,
  sys.z as curr_z,
  wsys.name AS next_waypoint,
  wsys.x as next_x,
  wsys.y as next_y,
  wsys.z as next_z,
  coalesce(inc.incidents, 0) as incidents,
  t.jumprange
FROM stwalkerster_ed_explore.session s
  INNER JOIN stwalkerster_ed_explore.system sys ON sys.id = s.currentsystem
  LEFT JOIN stwalkerster_ed_explore.waypoint w ON w.id = s.nextwaypoint
  LEFT JOIN stwalkerster_ed_explore.system wsys ON w.system = wsys.id
  LEFT JOIN (
    select i.session, sum(i.delta) incidents
from stwalkerster_ed_explore.incident i
inner join stwalkerster_ed_explore.incidenttype it on it.id = i.type
where it.technical = 0
group by i.session
    ) inc on inc.session = s.id
  INNER JOIN stwalkerster_ed_explore.trip t on t.id = s.trip
WHERE s.id = %d