SQL combine two queries result into one dataset

Question

I am trying to combine two SQL queries the first is

SELECT 
    EAC.Person.FirstName, 
    EAC.Person.Id,
    EAC.Person.LastName, 
    EAC.Person.EmployeeId, 
    EAC.Person.IsDeleted, 
    Controller.Cards.SiteCode, 
    Controller.Cards.CardCode, 
    Controller.Cards.ActivationDate, 
    Controller.Cards.ExpirationDate, 
    Controller.Cards.Status, 
    EAC.[Group].Name
FROM         
    EAC.Person 
INNER JOIN 
    Controller.Cards ON EAC.Person.Id = Controller.Cards.PersonId 
INNER JOIN 
    EAC.GroupPersonMap ON EAC.Person.Id = EAC.GroupPersonMap.PersonId 
INNER JOIN 
    EAC.[Group] ON EAC.GroupPersonMap.GroupId = EAC.[Group].Id

And the second one is

SELECT
    IsActive, ActivationDateUTC, ExpirationDateUTC, 
    Sitecode + '-' + Cardcode AS Credential, 'Badge' AS Type, 
    CASE 
       WHEN isActive = 0 
          THEN 'InActive' 
       WHEN ActivationDateUTC > GetUTCDate() 
          THEN 'Pending' 
       WHEN ExpirationDAteUTC < GetUTCDate() 
          THEN 'Expired' 
       ELSE 'Active' 
    END AS Status
FROM
    EAC.Credential
JOIN
    EAC.WiegandCredential ON Credential.ID = WiegandCredential.CredentialId
WHERE 
    PersonID = '32'

Where I would like to run the second query for each user of the first query using EAC.Person.Id instead of the '32'.

I would like all the data to be returned in one Dataset so I can use it in Report Builder.

I have been fighting with this all day and am hoping one of you smart guys can give me a hand. Thanks in advance.

Answer 1

Based on your description in the comments, I understand that the connection between the two datasets is actually the PersonID field, which exists in both EAC.Credential and EAC.Person; however, in EAC.Credential, duplicate values exist for PersonID, and you want only the most recent one for each PersonID.

There are a few ways to do this, and it will depend on the number of rows returned, the indexes, etc., but I think maybe you're looking for something like this...?

SELECT 
     EAC.Person.FirstName
    ,EAC.Person.Id
    ,EAC.Person.LastName
    ,EAC.Person.EmployeeId
    ,EAC.Person.IsDeleted
    ,Controller.Cards.SiteCode 
    ,Controller.Cards.CardCode
    ,Controller.Cards.ActivationDate
    ,Controller.Cards.ExpirationDate
    ,Controller.Cards.Status
    ,EAC.[Group].Name
    ,X.IsActive
    ,X.ActivationDateUTC
    ,X.ExpirationDateUTC
    ,X.Credential
    ,X.Type
    ,X.Status
FROM EAC.Person 
INNER JOIN Controller.Cards 
    ON EAC.Person.Id = Controller.Cards.PersonId 
INNER JOIN EAC.GroupPersonMap 
    ON EAC.Person.Id = EAC.GroupPersonMap.PersonId 
INNER JOIN EAC.[Group] 
    ON EAC.GroupPersonMap.GroupId = EAC.[Group].Id
CROSS APPLY 
    (
        SELECT TOP 1
             IsActive
            ,ActivationDateUTC
            ,ExpirationDateUTC
            ,Sitecode + '-' + Cardcode AS Credential
            ,'Badge' AS Type
            ,'Status' = 
                CASE
                    WHEN isActive = 0
                        THEN 'InActive'
                    WHEN ActivationDateUTC > GETUTCDATE()
                        THEN 'Pending'
                    WHEN ExpirationDateUTC < GETUTCDATE()
                        THEN 'Expired'
                    ELSE 'Active'
                END 
        FROM EAC.Credential
        INNER JOIN EAC.WiegandCredential
            ON EAC.Credential.ID = EAC.WiegandCredential.CredentialId 
        WHERE EAC.Credential.PersonID = EAC.Person.PersonID
        ORDER BY EAC.Credential.ID DESC
    ) AS X
-- Optionally, you can also add conditions to return specific rows, i.e.:
-- WHERE EAC.Person.PersonID = 32

This option uses a CROSS APPLY, which means that every row of the first dataset will return additional values from the second dataset, based on the criteria that you described. In this CROSS APPLY, I'm joining the two datasets based on the fact that PersonID exists in both EAC.Person (in your first dataset) as well as in EAC.Credential. I then specify that I want only the TOP 1 row for each PersonID, with an ORDER BY specifying that we want the most recent (highest) value of ID for each PersonID.

The CROSS APPLY is aliased as "X", so in your original SELECT you now have several values prefixed with the X. alias, which just means that you're taking these fields from the second query and attaching them to your original results.

CROSS APPLY requires that a matching entry exists in both subsets of data, much like an INNER JOIN, so you'll want to check and make sure that the relevant values exist and are returned correctly.

I think this is pretty close to the direction you're trying to go. If not, let me know and I'll update the answer. Good luck!