CREATE TABLE delegate(
   DELEGATE_REF      INTEGER  NOT NULL PRIMARY KEY
  ,code              INTEGER  NOT NULL
  ,name              VARCHAR(10) NOT NULL
  ,MEMBER_REF        INTEGER  NOT NULL
  ,TOTAL_AMOUNT      INTEGER  NOT NULL
  ,DELEGATE_SESS_REF INTEGER  NOT NULL
  ,EVENT_REF         INTEGER  NOT NULL
);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26174,51,'Delegate A',1077419,280,58136,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26183,52,'Delegate B',1110544,302,58157,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26206,53,'Delegate C',1084626,169,58209,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26210,54,'Delegate D',1092456,257,58218,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26212,55,'Delegate E',1055867,221,58223,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26220,56,'Delegate F',1109833,169,58240,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26229,57,'Delegate G',266050,0,58258,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26230,58,'Delegate H',1110868,0,58260,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26231,59,'Delegate I',1110890,0,58262,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26232,60,'Delegate J',1110891,0,58264,378);

CREATE TABLE event(
   code      VARCHAR(6) NOT NULL
  ,event_ref INTEGER  NOT NULL PRIMARY KEY
  ,name      VARCHAR(12) NOT NULL
);
INSERT INTO event(code,event_ref,name) VALUES ('AC2017',378,'MyConference');


CREATE TABLE delegate_session(
   DELEGATE_REF      INTEGER  NOT NULL
  ,DELEGATE_SESS_REF INTEGER  NOT NULL PRIMARY KEY
  ,SESSION_REF       INTEGER  NOT NULL
);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26183,58157,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26206,58209,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26212,58223,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26220,58240,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26229,58258,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26230,58260,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26231,58262,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26232,58264,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26174,58136,460);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF) VALUES (26210,58218,460);

CREATE TABLE session(
   SESSION_REF INTEGER  NOT NULL PRIMARY KEY
  ,NAME        VARCHAR(16) NOT NULL
);
INSERT INTO session(SESSION_REF,NAME) VALUES (460,'Delegate booking');

select e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, x1.t1 as 'Session 1'

from DELEGATE as d
INNER JOIN EVENT as e on d.EVENT_REF=e.EVENT_REF

left join (select d.DELEGATE_REF, s.name as 't1'
FROM DELEGATE as d
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where s.SESSION_REF=460
) as x1 on d.DELEGATE_REF=x1.delegate_ref

where d.code > 50 and d.code < 61 and e.code like 'ac2017'

group by e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, x1.t1