Ladestes Ladestes - 2 months ago 6
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)
);

CREATE TABLE event(
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

Answer

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

SELECT e.empID,
       e.Name,
       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)       
Comments