title: Joining two subqueries or a query with a subquery SQL Server 2008
body: <p>monster edit: The query will now run but returns incorrect answers.  Added a rough schema.  PatientID is not the primary key in the tblPatientVisits table because the same patientID can be present more than once.</p>
  
  <p>Every county name is listed, but each count(s.countyName) and count(t.countyname) as 1</p>
  
  <pre><code>PatientVisits   
  PatientID   int
  PatientState    varchar(2)
  patientCounty   varchar(3)
  visitNumber int - PK
  
  tblStateCounties    
  CPK stateCode   varchar(2)
  CPK countyCode  varchar(3)
  countyName  varchar(25)
  
  
  SELECT t.countyName,
      count(t.countyName) as reAdmits ,count(s.countyName) as totalVisits
  FROM (
      SELECT countyName,count(countyName) AS readmitCounts
      FROM (
          SELECT tblPatient.patientID
              ,tblStateCounties.countyName
          FROM tblPatient
          INNER JOIN tblPatientVisits
              ON tblPatient.patientID = tblPatientVisits.patientID
          INNER JOIN tblStateCounties
              ON tblPatientVisits.patientState = tblStateCounties.stateCode
                  AND tblPatientVisits.patientCounty = tblStateCounties.countyCode
          GROUP BY tblPatient.patientID
              ,tblStateCounties.stateCode
              ,tblStateCounties.countyName
          HAVING (COUNT(tblPatient.patientID) &gt; 1)
              AND (tblStateCounties.stateCode = '21')
          ) t
      GROUP BY countyname
      ) t
  INNER JOIN (
      SELECT countyName
      FROM (
          SELECT tblStateCounties.countyName
              ,COUNT(tblStateCounties.countyName) AS counts
          FROM tblPatient
          INNER JOIN tblPatientVisits
              ON tblPatient.patientID = tblPatientVisits.patientID
          INNER JOIN tblStateCounties
              ON tblPatientVisits.patientState = tblStateCounties.stateCode
                  AND tblPatientVisits.patientCounty = tblStateCounties.countyCode
          WHERE (tblStateCounties.stateCode = '21')
          GROUP BY tblStateCounties.countyName
          ) z
      ) s
      ON s.countyName = t.countyName
      group by s.countyname, t.countyname
  </code></pre>
  
  <p>edit: I have a query that runs now, but it returns </p>