Ladestes Ladestes - 4 months ago 15
MySQL Question

SQL - Generate column based on output

Here is my problem. I have a database with 2 strong entities (event and employee). Every event can have multiple employees as assistants and has one employee as leader. So I have a foreign key leaderID in event, and created a weak entity assistant connecting employee and event.

What i want is to show all employee name, adventureCode and startDate for each event and whether they are leaders or not. like:

GR01 2016/09/18 Trudy Lee Leader
GR01 2016/09/18 Sonia Chen Assistant
GR01 2016/09/18 Henry Blake Assistant
YV02 2016/09/17 John Bull Leader

I have no idea how to get that additional 4th column as its not in the database.

CREATE TABLE employee(
empID varchar(5) PRIMARY KEY,
name varchar(30)

adventureCode varchar(5),
startDate varchar(20),
leaderID varchar(5) NOT NULL,
PRIMARY KEY (adventureCode,startDate),
FOREIGN KEY (leaderID) REFERENCES employee (empID),
FOREIGN KEY (adventureCode) REFERENCES adventure (adventureCode)

CREATE TABLE assistant(
empID varchar(10),
startDate varchar(20),
adventureCode varchar(5),
PRIMARY KEY (empID,startDate,adventureCode),
FOREIGN KEY (empID) REFERENCES employee (empID),
FOREIGN KEY (adventureCode,startDate) REFERENCES event (adventureCode,startDate)

Any help would be welcome


You can use CASE EXPRESSION to generate this column, COALESCE() and a LEFT JOIN :

       COALESCE(ev.adventureCode,a.adventure_code) as adventure_code,
       COALESCE(ev.startDate,a.startdate) as startdate,
       CASE WHEN ev.leaderID IS NOT NULL THEN 'Leader' ELSE 'Assistant' END as pos_col
FROM employee e
LEFT JOIN `event` ev
 ON(ev.leaderID = e.empID)
LEFT JOIN assistant a
 ON(e.empID = a.empID)