Results 1 to 3 of 3

Thread: How To Combine These 2 Queries into 1 Query ?

  1. #1
    Join Date
    Jun 2012
    Posts
    1

    How To Combine These 2 Queries into 1 Query ?

    Code:
    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  3. #3
    Join Date
    Jun 2017
    Posts
    1
    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;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •