Querying data from different topics into one result set in T-SQL

Question

I do not have a better title for this, and I already found a solution - what I want to know is if there is a more elegant way to solve this, as I do not really like the way I did it.

The problem is: I have some items (let's call it persons) that are in m:n-relations with two different things (let's call them departments and associations). Let's assume Person 1 is member of Departments 1 and 2 and Association 1, Person 2 is member of Department 3 and Associations 1, 2 and 3, and Person 3 is member of Departments 1, 2 and 4 and Association 3. What I want is a result set that looks like

+==========+==============+===============+
| Person   | Department   | Association   |
+==========+==============+===============+
| Person 1 | Department 1 | Association 1 |
+----------+--------------+---------------+
| Person 1 | Department 2 | NULL          |
+----------+--------------+---------------+
| Person 2 | Department 3 | Association 1 |
+----------+--------------+---------------+
| Person 2 | NULL         | Association 2 |
+----------+--------------+---------------+
| Person 2 | NULL         | Association 3 |
+----------+--------------+---------------+
| Person 3 | Department 1 | Association 3 |
+----------+--------------+---------------+
| Person 3 | Department 2 | NULL          |
+----------+--------------+---------------+
| Person 3 | Department 4 | NULL          |
+----------+--------------+---------------+

What I did:

First I created 5 tables:


    CREATE TABLE Persons(
       PersonID int IDENTITY(1,1) NOT NULL,
       PersonName nvarchar(50) NOT NULL,
       CONSTRAINT PK_Persons PRIMARY KEY CLUSTERED (PersonID ASC)
    )

    CREATE TABLE Associations(
       AssociationID int IDENTITY(1,1) NOT NULL,
       AssociationName nvarchar(50) NOT NULL,
       CONSTRAINT PK_Associations PRIMARY KEY CLUSTERED (AssociationID ASC)
    )

    CREATE TABLE Departments(
       DepartmentID int IDENTITY(1,1) NOT NULL,
       DepartmentName nvarchar(50) NOT NULL,
       CONSTRAINT PK_Departments PRIMARY KEY CLUSTERED (DepartmentID ASC)
    )

    CREATE TABLE AssociationMembers(
       PersonID int NOT NULL,
       AssociationID int NOT NULL,
       CONSTRAINT PK_AssociationMembers PRIMARY KEY CLUSTERED (PersonID ASC, AssociationID ASC)
    )

    CREATE TABLE DepartmentMembers(
       PersonID int NOT NULL,
       DepartmentID int NOT NULL,
       CONSTRAINT PK_DepartmentMembers PRIMARY KEY CLUSTERED (PersonID ASC, DepartmentID ASC)
    )

Then I inserted some data:


    INSERT INTO Persons(PersonName) VALUES('Person 1')
    INSERT INTO Persons(PersonName) VALUES('Person 2')
    INSERT INTO Persons(PersonName) VALUES('Person 3')
    INSERT INTO Associations(AssociationName) VALUES ('Association 1')
    INSERT INTO Associations(AssociationName) VALUES ('Association 2')
    INSERT INTO Associations(AssociationName) VALUES ('Association 3')
    INSERT INTO Departments(DepartmentName) VALUES ('Department 1')
    INSERT INTO Departments(DepartmentName) VALUES ('Department 2')
    INSERT INTO Departments(DepartmentName) VALUES ('Department 3')
    INSERT INTO Departments(DepartmentName) VALUES ('Department 4')
    INSERT INTO AssociationMembers(PersonID, AssociationID) VALUES (1, 1)
    INSERT INTO AssociationMembers(PersonID, AssociationID) VALUES (2, 1)
    INSERT INTO AssociationMembers(PersonID, AssociationID) VALUES (2, 2)
    INSERT INTO AssociationMembers(PersonID, AssociationID) VALUES (2, 3)
    INSERT INTO AssociationMembers(PersonID, AssociationID) VALUES (3, 3)
    INSERT INTO DepartmentMembers(PersonID, DepartmentID) VALUES (1, 1)
    INSERT INTO DepartmentMembers(PersonID, DepartmentID) VALUES (1, 2)
    INSERT INTO DepartmentMembers(PersonID, DepartmentID) VALUES (2, 3)
    INSERT INTO DepartmentMembers(PersonID, DepartmentID) VALUES (3, 1)
    INSERT INTO DepartmentMembers(PersonID, DepartmentID) VALUES (3, 2)
    INSERT INTO DepartmentMembers(PersonID, DepartmentID) VALUES (3, 4)

and created two Views:


    CREATE VIEW v_AssociationMemberships
    AS
    SELECT
       p.PersonID,
       ROW_NUMBER() OVER (PARTITION BY p.PersonID ORDER BY a.AssociationID) AS 'AssociationRow',
       p.PersonName,
       a.AssociationID,
       a.AssociationName
    FROM
       dbo.Persons p
       INNER JOIN dbo.AssociationMembers am ON am.PersonID = p.PersonID
       INNER JOIN dbo.Associations a ON a.AssociationID = am.AssociationID

    CREATE VIEW v_DepartmentMemberships
    AS
    SELECT
       p.PersonID,
       ROW_NUMBER() OVER (PARTITION BY p.PersonID ORDER BY d.DepartmentID) AS 'DepartmentRow',
       p.PersonName,
       d.DepartmentID,
       d.DepartmentName
    FROM
       dbo.Persons p
       INNER JOIN dbo.DepartmentMembers dm ON dm.PersonID = p.PersonID
       INNER JOIN dbo.Departments d ON d.DepartmentID = dm.DepartmentID

This is how the final query looks now, and - it works, it's doing what it should do, but I wonder if there is a more simple, more elegant way to do it (using whatever T-SQL may offer):


    SELECT
       ISNULL(t.aPersonName, t.dPersonName) AS PersonName,
       t.dDepartmentName AS DepartmentName,
       t.aAssociationName AS AssociationName
    FROM (
       SELECT
          d.PersonName AS dPersonName,
          d.DepartmentName AS dDepartmentName,
          a.PersonName AS aPersonName,
          a.AssociationName AS aAssociationName
       FROM
          dbo.v_DepartmentMemberships d
          RIGHT OUTER JOIN dbo.v_AssociationMemberships a ON a.PersonID = d.PersonID AND a.AssociationRow = d.DepartmentRow
       UNION
       SELECT
          d.PersonName AS dPersonName,
          d.DepartmentName AS dDepartmentName,
          a.PersonName AS aPersonName,
          a.AssociationName AS aAssociationName
       FROM
          dbo.v_AssociationMemberships a
          RIGHT OUTER JOIN dbo.v_DepartmentMemberships d ON d.PersonID = a.PersonID AND d.DepartmentRow = a.AssociationRow
    ) t
    ORDER BY
       PersonName,
       ISNULL(t.dDepartmentName, 'zzzzzzzzzzzzz'),
       ISNULL(t.aAssociationName, 'zzzzzzzzzzzzz')

Thanks - and maybe someone can suggets a better title for this question as well.

Daniel Gimenez Daniel Gimenez · Accepted Answer · 2018-08-27T18:12:41

You have tables in your query that aren't related to each other - Associations and Departments - and you don't want the result to have a cross product of the two tables. So you need to create new tables, views, subqueries or CTEs that relate the two tables in such a way that only one value is displayed per Person.

I think your idea to use ROW_NUMBER was the right idea, but you went about it in a round about way. In this answer I use CTEs to append a unique ROW_NUMBER to Associations and Departments for each member associated to a person, and then use another CTE to create the result set used to combine Associations and Departments to Persons.

WITH am AS (
    SELECT PersonID, am.AssociationID
        , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY am.AssociationID) PersonRowID
    FROM AssociationMembers am
)
, dm AS (
    SELECT PersonID, dm.DepartmentID
        , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY dm.DepartmentID) PersonRowID
    FROM DepartmentMembers dm
)
, personRows AS (
    SELECT PersonID, PersonRowID FROM am 
    UNION 
    SELECT PersonID, PersonRowID FROM dm
)
SELECT p.PersonName, d.DepartmentName, a.AssociationName
FROM personRows
INNER JOIN Persons p ON p.PersonID = personRows.PersonID
LEFT JOIN am ON am.PersonID = p.PersonID AND am.PersonRowID = personRows.PersonRowID
LEFT JOIN Associations a ON a.AssociationID = am.AssociationID
LEFT JOIN dm ON dm.PersonID = p.PersonID AND dm.PersonRowID = personRows.PersonRowID
LEFT JOIN Departments d ON d.DepartmentID = dm.DepartmentID

The personRows CTE takes advantage of the fact that UNION will keep only distinct values, so that PersonRowID for each person will have one unique value up to the max.

Querying data from different topics into one result set in T-SQL

1 Answers