title: SQL combine two queries result into one dataset
body: <p>I am trying to combine two SQL queries the first is</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And the second one is</p>
  
  <pre><code>SELECT
      IsActive, ActivationDateUTC, ExpirationDateUTC, 
      Sitecode + '-' + Cardcode AS Credential, 'Badge' AS Type, 
      CASE 
         WHEN isActive = 0 
            THEN 'InActive' 
         WHEN ActivationDateUTC &gt; GetUTCDate() 
            THEN 'Pending' 
         WHEN ExpirationDAteUTC &lt; GetUTCDate() 
            THEN 'Expired' 
         ELSE 'Active' 
      END AS Status
  FROM
      EAC.Credential
  JOIN
      EAC.WiegandCredential ON Credential.ID = WiegandCredential.CredentialId
  WHERE 
      PersonID = '32'
  </code></pre>
  
  <p>Where I would like to run the second query for each user of the first query using EAC.Person.Id instead of the '32'.</p>
  
  <p>I would like all the data to be returned in one Dataset so I can use it in Report Builder.</p>
  
  <p>I have been fighting with this all day and am hoping one of you smart guys can give me a hand. Thanks in advance.</p>