SQL Union Query

SELECT pv.PropertyID, COUNT(pv.VisitID) AS InitialVisit
FROM tblPAppointments pa INNER JOIN tblPropertyVisit pv ON pv.AppID = pa.AppID
WHERE pv.Status = 0
GROUP BY pv.PropertyID

UNION ALL

SELECT jv.Prope开发者_如何学CrtyID, COUNT(jv.JobVistID) AS JobVisit
FROM tblPAppointments pa INNER JOIN tblJobVisits jv ON jv.AppID = pa.AppID
WHERE jv.VisitStatus = 1
GROUP BY jv.PropertyID

I need to get InitialVisit count and JobVisit count in two separate columns.above query returns just two columns (PropertyID,InitialVisit).

---

Use a NULL as a placeholder for the column that there won't be any output for:

  SELECT pv.PropertyID, 
         COUNT(pv.VisitID) AS InitialVisit,
         NULL AS jobvisit
    FROM tblPAppointments pa 
    JOIN tblPropertyVisit pv ON pv.AppID = pa.AppID
   WHERE pv.Status = 0
GROUP BY pv.PropertyID
UNION ALL
  SELECT jv.PropertyID, 
         NULL AS initialvisit,
         COUNT(jv.JobVistID) AS JobVisit
    FROM tblPAppointments pa 
    JOIN tblJobVisits jv ON jv.AppID = pa.AppID
   WHERE jv.VisitStatus = 1
GROUP BY jv.PropertyID

This will return three columns. The column alias is necessary in the first query, but not in the second -- I aliased both to make it clear what is happening.

Be aware that using NULL like this in SQL Server will require you to use CAST/CONVERT on the NULL for data types other than INT because SQL Server defaults the NULL to an INT data type (as odd as that is).

An alternate query that doesn't use UNION:

   SELECT x.propertyid,
          COUNT(y.visitid) AS initialvisit,
          COUNT(z.jobvisitid) AS jobvisit
     FROM (SELECT pv.propertyid
             FROM TBLPROPERTYVISIT pv
            WHERE EXISTS (SELECT NULL
                            FROM TBLAPPOINTMENTS a 
                           WHERE a.appid = pv.appid)
           UNION 
           SELECT jv.propertyid
             FROM TBLJOBVISIT jv
            WHERE EXISTS (SELECT NULL
                            FROM TBLAPPOINTMENTS a 
                           WHERE a.appid = jv.appid)) x
LEFT JOIN TBLPROPERTYVISIT y ON y.propertyid = x.propertyid
LEFT JOIN TBLJOBVISIT z ON z.propertyid = x.propertyid
 GROUP BY x.propertyid

---

No need for a UNION at all. And you don't use tblPAppointments either

Edited to allow for no rows in one of the tables. Still one row output though

SELECT
    ISNULL(pv2.PropertyID, jv2.PropertyID),
    ISNULL(pv2.InitialVisit, 0),
    ISNULL(jv2.JobVisit, 0)
FROM
    (
    SELECT pv.PropertyID, COUNT(pv.VisitID) AS InitialVisit
    FROM tblPropertyVisit pv
    WHERE pv.Status = 0
    GROUP BY pv.PropertyID
    ) pv2
    FULL OUTER JOIN
    (
    SELECT jv.PropertyID, COUNT(jv.JobVistID) AS JobVisit
    FROM tblJobVisits jv
    WHERE jv.VisitStatus = 1
    GROUP BY jv.PropertyID
    ) jv2 ON pv2.PropertyID = jv2.PropertyID