locked
Full Outer Join help with Access 2003 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User521235944 posted

    I am using Access as my database for an ASP.NET project/Vb.NET.  However it seems as though Access does not support Full Outer Joins.


    Basically I have two tables in one query used to populate a Gridview.  I need to list all available companies and their employee count even if that Company does not have any employees and return the result as 0.  Is there anyway I can do this in an Access query.  The relationship layout is the tblCompany has a 1 to Many relationship with the Employee table.   

    Below is what I would like have happen however access does not seem to recognize FULL OUTER JOINS.

    SELECT DISTINCTROW tblCompany.Comp_Name, tblCompany.Comp_CompanyID, tblCompany.Comp_CreatedOn, tblCompany.Comp_Status, Count(*) AS [Employee Count]
    FROM tblCompany FULL OUTER  JOIN tblEmployee ON tblCompany.Comp_CompanyID = tblEmployee.Comp_CompanyID
    GROUP BY tblCompany.Comp_Name, tblCompany.Comp_CompanyID, tblCompany.Comp_CreatedOn, tblCompany.Comp_Status;


    Thanks

    Sunday, May 9, 2010 11:57 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User840877147 posted

    This will work for sure:

    SELECT
	a.Comp_Name, a.Comp_CompanyID, a.Comp_CreatedOn, a.Comp_Status,
	b.[Employee Count]
FROM
	tblCompany a
	LEFT JOIN 
	( SELECT COUNT(*)[Employee Count],
	  Comp_CompanyID
	  FROM tblEmployee
	  GROUP BY Comp_CompanyID ) b
ON
	a.Comp_CompanyID = b.Comp_CompanyID


     As this is a left join companies those dont have any employee will show Employee Count as null. In mssql we can use ISNULL(Column, 0) to replace null to 0. Find the equivalent (if not same) in access and apply that to the column b.[Employee Count] in the select list.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 10, 2010 1:46 AM