Post reply

Multiple Joins With Varying Number of Selected Items

  • October 7, 2009 at 8:36 pm

    #136476

    Hi,

    I need to write a query for an application where users can filter on multiple criteria (up to 50!) but the number of selected items varies and the criteria are in separate rows, so I need to do multiple joins to the same table (alias). This is on SQL Server 2005 with an ASP.NET web app.

    For example, I need to join 3 tables (Employee, EmployeeOrg, and Org) structured below. There's a radiobuttonlist (AND, OR, AND/OR) for users to find employees based on the Org they are assigned to. If the user selects AND in the radiobuttonlist, I need to return all Employees that have that meet all the criteria (i.e., belong to each organization checked). It's not feasible to write up to 50 same-table joins every time. A dynamic query might work but it would be extremely clunky. I'm not sure what the best approach would be. There's a detailed article that suggests using a pivot table (http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/%5B/url%5D), but I'm wondering if it's too complicated for what I'm trying to do.

    It works when you know the number of items (say 3), then the query looks like this. But how do you construct one that doesn't use dynamic queries or require 50 same-table joins?

    Example: user wants to return all employees that are assigned PTA AND IEEE AND AAA, this query works:

    select E.EmpName, E.EmpID

    from Employee E, EmployeeOrg AS EO, EmployeeOrg AS EO2, EmployeeOrg AS EO3

    where E.EmpID = EO.EmpID

    and EO.OrgID = '1'

    and EO2.EmpID = E.EmpID

    and EO2.OrgID = '2'

    and EO3.EmpID = E.EmpID

    and EO3.OrgID = '3'

    Data:

    EmpID EmpName

    --------------

    1001 M. Scott

    1002 P. Beesly

    1003 D. Schrute

    EmpID OrgID

    ------------------

    1001 1

    1001 2

    1001 3

    1002 2

    1003 1

    OrgID OrgName

    ------------------

    1 PTA

    2 IEEE

    3 AAA

    Desired output would be:

    EmpName EmpID

    ----------------------------

    M. Scott 1001

    Any help or pointers would be appreciated! Thank you

  • October 7, 2009 at 9:41 pm

    #1063227

    I received the solution from another forum:

    SELECT EmpName, E.EmpID

    FROM Employee AS E

    JOIN EmployeeOrg AS EO

    ON E.EmpID = EO.EmpID

    WHERE OrgID IN ('1', '2', '3')

    GROUP BY EmpName, E.EmpID

    HAVING COUNT(DISTINCT OrgID) = 3

    🙂

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply