Third join to get first_Name and Last_Name of account

gistfile1.sql

CREATE TABLE `Project_Assigned` (
  `AccountID` INT NOT NULL,
  `ProjectID` INT NOT NULL,
  PRIMARY KEY (`AccountID`, `ProjectID`)
);


CREATE TABLE `Account` (
  `Account_ID` INT NOT NULL,
  `Username` VARCHAR(45) NULL,
  PRIMARY KEY (`Account_ID`)
);


CREATE TABLE `Project` (
  `Project_ID` INT NOT NULL,
  `Project_Title` VARCHAR(45) NULL,
  PRIMARY KEY (`Project_ID`)
);


CREATE TABLE `Client` (
  `Client_ID` INT NOT NULL,
  `First_Name` VARCHAR(45) NULL,
  `Last_Name` VARCHAR(45) NULL,
  PRIMARY KEY (`Client_ID`)
);


CREATE TABLE `Employee` (
  `Employee_ID` INT NOT NULL,
  `First_Name` VARCHAR(45) NULL,
  `Last_Name` VARCHAR(45) NULL,
  PRIMARY KEY (`Employee_ID`)
);


INSERT INTO `Client` VALUES (4, 'Phil', 'Jones');

INSERT INTO `Employee` VALUES (2, 'Richard', 'Malcolm');
INSERT INTO `Employee` VALUES (5, 'Bob', 'Jones');
INSERT INTO `Employee` VALUES (6, 'Fred', 'Tucker');

INSERT INTO `Account` VALUES (1, 'Malcr001');
INSERT INTO `Account` VALUES (2, 'rjm');
INSERT INTO `Account` VALUES (3, 'pw');
INSERT INTO `Account` VALUES (4, 'Philly');
INSERT INTO `Account` VALUES (5, 'bob');
INSERT INTO `Account` VALUES (6, 'fred');

INSERT INTO `Project` VALUES (1, 'Project A');
INSERT INTO `Project` VALUES (2, 'Project B');
INSERT INTO `Project` VALUES (3, 'Project C');
INSERT INTO `Project` VALUES (4, 'Project D');

INSERT INTO `Project_Assigned` (ProjectID, AccountID) VALUES (1, 1);
INSERT INTO `Project_Assigned` (ProjectID, AccountID) VALUES (1, 2);
INSERT INTO `Project_Assigned` (ProjectID, AccountID) VALUES (1, 4);


SELECT 
    proj.ProjectID, 
    Project.Project_Title, 
    Account.Account_ID, Account.Username,
    COALESCE(Client.First_Name, Employee.First_Name),
    COALESCE(Client.Last_Name, Employee.Last_Name)

FROM `Project_Assigned` proj
INNER JOIN `Account` ON (proj.AccountID = Account.Account_ID)
INNER JOIN `Project` ON (proj.ProjectID = Project.Project_ID)
LEFT JOIN `Client` ON (Account.Account_ID = Client.Client_ID)
LEFT JOIN `Employee` ON (Account.Account_ID = Employee.Employee_ID)
WHERE proj.ProjectID = 1;



SELECT 
    proj.ProjectID, 
    Project.Project_Title, 
    Account.Account_ID, Account.Username,
    Client.First_Name AS Client_First_Name, Client.Last_Name AS Client_Last_Name,
    Employee.First_Name AS Employee_First_Name, Employee.Last_Name AS Employee_Last_Name

FROM `Project_Assigned` proj
INNER JOIN `Account` ON (proj.AccountID = Account.Account_ID)
INNER JOIN `Project` ON (proj.ProjectID = Project.Project_ID)
LEFT JOIN `Client` ON (Account.Account_ID = Client.Client_ID)
LEFT JOIN `Employee` ON (Account.Account_ID = Employee.Employee_ID)
WHERE proj.ProjectID = 1;