Results 1 to 3 of 3
  1. #1
    sfgiantsdude is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    34

    adding left join to pass thru query from data warehouse

    I have a pass-thru query that is pulling data from a data warehouse using sql. I'm trying to add a table (KP_EXTL_CASE) using a left join but not sure how to integrate that into the existing code framework that declares each table as a letter and where each table is linked via a where statement. I want to label KP_EXTL_CASE as "L". Please see attached. Thanks!


    FROM HPEPP20.PS_KP_DW_ISU A,
    HPEPP20.PS_KP_DW_ISU_CTGRY B,
    HPEPP20.PS_KP_DW_LVL_CTGRY C,
    HPEPP20.PS_KP_DW_PRVDR_GRP D,
    HPEPP20.PS_KP_DW_DEPT_ORG F,
    HPEPP20.PS_KP_DW_TM_PER I,
    HPEPP20.PS_KP_DW_MBR_INFO E,
    HPEPP20.PS_KP_DW_FAC_ORG G,
    HPEPP20.PS_KP_DW_PRVDR H,
    HPEPP20.PS_KP_ISU_DESCR J,


    HPEPP20.PS_KP_DW_MBR_CTGRY K,
    LEFT JOIN HPEPP20.PS_KP_EXTL_CASE L ON A.CASE_ID = L.CASE_ID
    WHERE B.KP_ISSU_CTGRY_IK = A.KP_ISSU_CTGRY_IK
    AND C.KP_LVL_CTGRY_IK = A.KP_LVL_CTGRY_IK
    AND K.KP_MBR_CTGRY_IK = A.KP_MBR_CTGRY_IK
    AND D.KP_PRVDR_GRP_IK = A.KP_PRVDR_GRP_IK
    Attached Thumbnails Attached Thumbnails 2018-02-21_9-43-21.png  
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    select
    ...
    From hpepp20.ps_kp_dw_isu a
    left join hpepp20.ps_kp_extl_case l on l.case_id = a.case_id,
    hpepp20.ps_kp_dw_isu_ctdry b,
    ...
    OK. Somehow the site replaced all upper case to proper case when saving

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    all your other tables are not joined (called a Cartesian query), you cannot mix Cartesian and outer (left or right) joins in the same query

    without knowing the full query I would expect something like

    SELECT L.KP..., Z.* FROM
    HPEPP20.PS_KP_EXTL_CASE L LEFT JOIN
    (SELECT
    A.KP....
    E.KP..
    K.KP..
    FROM HPEPP20.PS_KP_DW_ISU A,
    HPEPP20.PS_KP_DW_ISU_CTGRY B,
    HPEPP20.PS_KP_DW_LVL_CTGRY C,
    HPEPP20.PS_KP_DW_PRVDR_GRP D,
    HPEPP20.PS_KP_DW_DEPT_ORG F,
    HPEPP20.PS_KP_DW_TM_PER I,
    HPEPP20.PS_KP_DW_MBR_INFO E,
    HPEPP20.PS_KP_DW_FAC_ORG G,
    HPEPP20.PS_KP_DW_PRVDR H,
    HPEPP20.PS_KP_ISU_DESCR J,
    HPEPP20.PS_KP_DW_MBR_CTGRY K

    WHERE
    B.KP_ISSU_CTGRY_IK = A.KP_ISSU_CTGRY_IK
    AND C.KP_LVL_CTGRY_IK = A.KP_LVL_CTGRY_IK
    AND K.KP_MBR_CTGRY_IK = A.KP_MBR_CTGRY_IK
    AND D.KP_PRVDR_GRP_IK = A.KP_PRVDR_GRP_IK) Z ON Z.CASE_ID = L.CASE_ID

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-20-2017, 09:50 AM
  2. Replies: 3
    Last Post: 04-15-2016, 12:19 PM
  3. Left Join Query Issue - MS Access
    By mkc80 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 04:17 PM
  4. Replies: 7
    Last Post: 09-06-2012, 06:04 AM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 PM

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
  •  
Other Forums: Microsoft Office Forums