Help with Query

wildgoosed · #1 (**permalink**) 12-23-11, 11:57

Hi everyone,

I have 3 tables.

Employees(empid)
EmployeesCertificates(empid,certid,expirydate)
Certificates(certid)

What I'm trying to do is return all certificates for all employees, but I also want to return entries where employee's are missing certificates.

So far this is what I have but it only returns certificates that employee's have.

Code:

select EmployeeCertificates.EMPID, EmployeeCertificates.CertId,EmployeeCertificates.ExpiryDate, Certificates.CertID,Certificates.CertName
from EmployeesCertificates
left join Certificates on
EmployeeCertificates.CertId = Certificates.CertID

I imagine I'm missing something with the Employee's table maybe, not sure though. Any advice would be appreciated.

Thanks

Wim · #2 (**permalink**) 12-23-11, 13:18

Try this:

Code:

select Employees.EMPID, 
	EmployeeCertificates.CertId,
	EmployeeCertificates.ExpiryDate, 
	Certificates.CertID,
	Certificates.CertName
from Employees
	LEFT OUTER JOIN EmployeesCertificates ON
		Employees.empid = EmployeesCertificates.empid
	left OUTER join Certificates on
		EmployeeCertificates.CertId = Certificates.CertID

wildgoosed · #3 (**permalink**) 12-23-11, 18:31

Hi Wim!

Thank you very much. I actually had something very similar this morning, and changed my mind, so I was sort of close.

Internet Services

Web Development

Digital Marketing

Consumer Tech