CODEWITHSUNDEEP
sqlms-access
Bishonen_PL
Bishonen_PL

Reputation: 1571

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!

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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 e.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.

Upvotes: 2

Related Questions