How To Combine These 2 Queries into 1 Query ?

[tarek_land]

SELECT b.KPCNO
         ,b.KPC_FULL_NAME
         ,min(c.time) in_time

       FROM xxkpc_hr_personnel_v2  b
               ,xxkpc_fingerscan_data_v c 
       WHERE to_char(c.userpin(+)) = b.KPCNO 
       AND b.type(+) = 'KPC Employee'
       AND b.DESIGNATION is null
       AND b.kpcno IS NOT NULL
       AND to_char(c.trdate(+),'DD-MON-YYYY') = to_char(SYSDATE,'DD-MON-YYYY')
       AND c.tr_type(+) = 'Admitted In'
       AND c.unitno(+) not in ('Staff Exit Barrier','Staff Entry Barrier 1','Service Gate Entry Barrier','Staff Entry Barrier 2')

      AND b.ORGANIZATION_ID=(select g.ORGANIZATION_ID from  xxkpc_fn_web_personnel_v g where g.kpcno = :kpcno)
           GROUP BY b.KPCNO
          ,b.kpc_full_name


              UNION




 SELECT b.KPCNO
         ,b.KPC_FULL_NAME
         ,min(c.time) in_time

       FROM xxkpc_hr_pos_struct_code_comb   a
               ,xxkpc_hr_personnel_v2  b
               ,xxkpc_fingerscan_data_v 
       WHERE b.position_id(+) = a.child_position_id
       AND to_char(c.userpin(+)) = b.KPCNO  
       AND b.type(+) = 'KPC Employee'
       AND b.kpcno IS NOT NULL
       AND to_char(c.trdate(+),'DD-MON-YYYY') = to_char(SYSDATE,'DD-MON-YYYY')
       AND c.tr_type(+) = 'Admitted In'
       AND c.unitno(+) not in ('Staff Exit Barrier','Staff Entry Barrier 1','Service Gate Entry Barrier','Staff Entry Barrier 2')
       AND a.PERANT_POSITION_ID =  (select g.POSITION_ID from  xxkpc_fn_web_personnel_v g where g.kpcno = :kpcno)

           GROUP BY b.KPCNO
          ,b.kpc_full_name

[skhanal]

Are you getting any error with UNION?

[Kevin_C]

I have a similar issue. Query 1 returns 417 records from 3 tables. Query 2 returns 399 records from 5 tables. That means 18 records are missing a Contact Person. I know I have to use a Union, and the number of columns has to be the same. But, when I add the additional columns to Query 1, and add the 2 additional tables, I always end up with the 399 records and never get the extra 18. There are a couple of linking tables, Any help?

--Query 1
SELECT A.ID "Site ID",
C.ALT_ID_NO "Facility ID",
B.INTEREST_TYPE "Interest Type",
A.NAME "Facility Name",
A.ADDR_1 "Facility Address",
A.CITY_NAME "Facility City",
A.STATE_CODE "Facility State",
A.POSTAL_CODE "Facility Zip Code",
A.COUNTY_NAME "County Name"

FROM E_SITES A
LEFT JOIN INTERESTS B ON A.ID = B.SITE_ID
LEFT JOIN ALT_IDS C ON B.RID = C.INTEREST_RID

WHERE A.ID = C.SITE_ID
AND A.APP_RID = C.APP_RID
AND C.RID = B.INTEREST_RID
AND A.APP_RID = -1
AND (A.COUNTY_NAME IN ('Allamakee', 'Benton', 'Black Hawk', 'Bremer', 'Buchanan', 'Chickasaw', 'Clayton'))

--insert
UNION ALL
--here

--Query 2
SELECT A.ID "Site ID",
C.ALT_ID_NO "Facility ID",
B.INTEREST_TYPE "Interest Type",
A.NAME "Facility Name",
A.ADDR_1 "Facility Address",
A.CITY_NAME "Facility City",
A.STATE_CODE "Facility State",
A.POSTAL_CODE "Facility Zip Code",
A.COUNTY_NAME "County Name",
E.FIRST_NAME "First Name",
E.LAST_NAME "Last Name",
E.MIDDLE_INITIAL "MI",
D.PHONE_NO "Contact Ph",
D.PHONE_EXT_NO "Ph Exten"

FROM E_SITES A
LEFT JOIN E_INTERESTS B ON A.ID = B.SITE_ID
LEFT JOIN E_ALT_IDS C ON B.RID = C.INTEREST_RID
LEFT JOIN E_AFFILIATIONS D ON A.ID = D.SITE_ID
LEFT JOIN E_INDIVIDUALS E ON D.INDIVIDUAL_RID = E.RID

WHERE A.APP_RID = B.APP_RID
AND A.APP_RID = D.APP_RID
AND A.APP_RID = -1
AND D.AFFILIATION_TYPE = ('FACILITY CONTACT'))
AND A.COUNTY_NAME IN ('Allamakee', 'Benton', 'Black Hawk', 'Bremer', 'Buchanan', 'Chickasaw', 'Clayton')
ORDER BY C.ALT_ID_NO;