CODEWITHSUNDEEP
mysql
Malcr001
Malcr001

Reputation: 8289

Third join to get first_Name and Last_Name of account

I have the following query which matches Account_ID's on Accounts table with the AccountID's on project assigned table and displays the accounts assigned to a project:

SELECT proj.ProjectID, A.Project_Title, B.Account_ID, B.Username, B.Access_Type
FROM Project_Assigned proj
INNER JOIN Account B
    ON proj.AccountID = B.Account_ID
INNER JOIN Project A
    ON proj.ProjectID = A.Project_ID
WHERE proj.ProjectID = 1;

What I want to do now is get the First_Name, Last_Name from Client table and Agency_Employee table and display the information matched against the Account_ID's. Both Client_ID and Employee_ID are foreign keys of Account_ID. How would I add this information into the join above?

I have attempted to add an additional join but I always get a result set match 0 which I know shouldn't be the case.

Client Table:

+-----------+------------+-----------+
| Client_ID | First_Name | Last_Name |
+-----------+------------+-----------+
|         4 | Phil       | Jones     |
+-----------+------------+-----------+

Employee Table:

+-------------+------------+-----------+
| Employee_ID | First_Name | Last_Name |
+-------------+------------+-----------+
|           2 | John       | Smith     |
|           5 | Bob        | Jones     |
|           6 | Fred       | Tucker    |
+-------------+------------+-----------+

Account Table:

+------------+----------+
| Account_ID | Username |
+------------+----------+
|          1 | Dan      |
|          2 | rjm      |
|          3 | pw       |
|          4 | Philly   |
|          5 | bob      |
|          6 | fred     |
+------------+----------+

Project Assigned Table:

+-----------+-----------+
| ProjectID | AccountID |
+-----------+-----------+
|         1 |         1 |
|         1 |         2 |
|         1 |         4 |
+-----------+-----------+

Upvotes: 2

Views: 406

Answers (2)

Cylindric
Cylindric

Reputation: 5894

I'm not sure, but you seem to indicate that for an Account with ID 5, the Client will have ID 5 and the Employee will have ID 5? Slightly odd, and you probably want to read up on normalisation, but shouldn't this work?

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;

edit Okay, I've updated my answer. You'll have two first names and two lastnames, one of each will always be null.

If you only want one contact name, try this:

SELECT 
    COALESCE(Client.First_Name, Employee.First_Name) FirstName,
    COALESCE(Client.Last_Name, Employee.Last_Name) LastName

Upvotes: 2

Siva Charan
Siva Charan

Reputation: 18064

SELECT c.First_Name, c.Last_Name, e.First_Name, e.Last_Name 
FROM client c, employee e, account a 
WHERE c.Client_ID = a.Account_ID OR e.Employee_ID = a.Account_ID

OR

SELECT c.First_Name, c.Last_Name
FROM client c, account a 
WHERE c.Client_ID = a.Account_ID    
UNION    
SELECT e.First_Name, e.Last_Name 
FROM employee e, account a 
WHERE e.Employee_ID = a.Account_ID

Upvotes: 0

Related Questions