CODEWITHSUNDEEP
sqloracle-database
user3586248
user3586248

Reputation: 163

How to combine 2 columns of sql results into 1

I am pretty stumped on this one. I am getting two columns of sql results for emplid but don't know how to just combine them into 1. Anyone know how? I'm using oracle. Here is the sql:

SELECT ee.emplid, mem.emplid
  FROM ps_employees ee, ps_vc_plan_mem mem
 WHERE     ee.empl_status IN ('A', 'L')
       AND ee.emplid NOT IN (SELECT vc.emplid
                               FROM ps_vc_plan_mem vc
                              WHERE vc.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP'))
       AND ee.emplid IN
              (SELECT srv.emplid
                 FROM ps_Z_ADD_EMP_SRVDT srv
                WHERE     srv.emplid = ee.emplid
                      AND srv.z_serv_dt_type = 'ESP'
                      AND service_dt <= (SELECT asofdate FROM psasofdate))
   OR mem.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP')

Upvotes: 0

Views: 132

Answers (2)

SlimsGhost
SlimsGhost

Reputation: 2909

In that case, you want a UNION, which is one result set (the first column) concatenated with another result set (the second column). It would look something like this:

SELECT ee.emplid
FROM ps_employees ee, ps_vc_plan_mem mem
WHERE     ee.empl_status IN ('A', 'L')
AND ee.emplid NOT IN (SELECT vc.emplid
                           FROM ps_vc_plan_mem vc
                          WHERE vc.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP'))
AND ee.emplid IN
          (SELECT srv.emplid
             FROM ps_Z_ADD_EMP_SRVDT srv
            WHERE     srv.emplid = ee.emplid
                  AND srv.z_serv_dt_type = 'ESP'
                  AND service_dt <= (SELECT asofdate FROM psasofdate))
OR mem.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP')
--Here's the use of UNION
UNION ALL
--Seond query is the same, just returning the "other" column
SELECT mem.emplid
FROM ps_employees ee, ps_vc_plan_mem mem
WHERE     ee.empl_status IN ('A', 'L')
AND ee.emplid NOT IN (SELECT vc.emplid
                           FROM ps_vc_plan_mem vc
                          WHERE vc.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP'))
AND ee.emplid IN
          (SELECT srv.emplid
             FROM ps_Z_ADD_EMP_SRVDT srv
            WHERE     srv.emplid = ee.emplid
                  AND srv.z_serv_dt_type = 'ESP'
                  AND service_dt <= (SELECT asofdate FROM psasofdate))
OR mem.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP')

If you want your result to be distinct values, use UNION instead of UNION ALL, but other than that, you're pretty much set.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

If I'm following the logic you just want to split your current cross-join out into separate queries and union the results together; and you can lose one of the filters along the way as it's redundant:

SELECT ee.emplid,
  FROM ps_employees ee
 WHERE     ee.empl_status IN ('A', 'L')
       AND ee.emplid IN
              (SELECT srv.emplid
                 FROM ps_Z_ADD_EMP_SRVDT srv
                WHERE     srv.emplid = ee.emplid
                      AND srv.z_serv_dt_type = 'ESP'
                      AND service_dt <= (SELECT asofdate FROM psasofdate))
UNION ALL
SELECT mem.emplid
  FROM ps_vc_plan_mem mem
 WHERE mem.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP')

The union eliminates duplicates, so you don't need to explicitly exclude values from the first half that appear in the second half.

If either part of the query can produce duplicates on its own - the same ee.emplid can appear twice, say - then this will suppress those duplicates too. If they can exist and you want to show them then you'd need to use union all instead, and you might in that case want to reinstate the not exists clause.

Read more about the union operator.

If you wanted an overall count then you could wrap this as an inline view:

SELECT COUNT(*)
FROM (
    SELECT ee.emplid,
      FROM ps_employees ee
     WHERE     ee.empl_status IN ('A', 'L')
           AND ee.emplid IN
                  (SELECT srv.emplid
                     FROM ps_Z_ADD_EMP_SRVDT srv
                    WHERE     srv.emplid = ee.emplid
                          AND srv.z_serv_dt_type = 'ESP'
                          AND service_dt <= (SELECT asofdate FROM psasofdate))
    UNION ALL
    SELECT mem.emplid
      FROM ps_vc_plan_mem mem
     WHERE mem.vc_plan_id in ('PNC-RS','PNC-SO','PNC-ESPP')
)

Upvotes: 1

Related Questions