Bishonen_PL Bishonen_PL - 9 months ago 42
SQL Question

SQL - Select Field to write depending on record

the following code works as intended:

SELECT Sessions.*, Register.EmailAddress AS TeamleadAddress
FROM Register, Employees INNER JOIN Sessions ON Employees.EmpNumber = Sessions.EmpNumber
WHERE (((Register.Username)=[Employees].[Teamlead]));

There's another column I need to the same query though:

Register.EmailAddress AS QualityAddress WHERE ((Register.Username)=[Employees].[Quality]));

Sadly I don't really can figure it by myself... Thanks in advance!

Answer Source

You need an additional JOIN to Register. In MS Access, this also requires a bunch of parentheses:

SELECT s.*, rtl.EmailAddress AS TeamleadAddress, rq.EmailAddress
FROM ((Sessions as s LEFT JOIN
       Employees as e
       ON etl.EmpNumber =  s.EmpNumber
      ) LEFT JOIN
      Register as rtl
      ON rtl.UserName = e.Teamlead
     ) LEFT JOIN
     Register as rq
     ON rq.UserName = e.Quality;

This uses LEFT JOIN, so if one of the columns is NULL in Employees, then the query will still return the row.