Results 1 to 3 of 3

Thread: Help with Query

  1. #1
    Join Date
    Aug 2007
    Posts
    7

    Unanswered: Help with Query

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2007
    Posts
    7
    Hi Wim!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •