1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Join on a joined column

Discussion in 'General' started by Sparamanga, Jul 8, 2018.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi,

    I can't work out why this doesn't work as I'm sure I usually use joined columns to join to other tables.

    In essence I mean i will select columns from one table and join to another table and bring another column over. I then want to use the joined column to join to another table and bring another column over.

    It works when i use a cte to encapuslate the first part of the query and then reference the joined column by its aliased name as so...

    with cte as (
    SELECT
    MSF720.WORK_GROUP, MSF720.WORK_GRP_DESC, MSF720.WORK_GROUP_SET,
    MSF010.TABLE_DESC AS WGST_DESC,
    SUBSTR(MSF010.ASSOC_REC, 3,6) AS MAINT_ENG_CODE
    FROM ADS_FOUNDATION.ADS_FND_msf720 MSF720
    LEFT JOIN ADS_FOUNDATION.ADS_FND_MSF010 MSF010 ON TRIM(MSF720.WORK_GROUP_SET)=TRIM(MSF010.TABLE_CODE) AND TRIM(MSF010.TABLE_TYPE)='WGST' AND MSF010.ACTIVE_ROW_FLAG='Y' AND TRIM(MSF010.TABLE_TYPE)='WGST'
    )
    select cte.*, msf010.table_desc as MAINT_ENG_DESC from cte
    LEFT JOIN ADS_FOUNDATION.ADS_FND_MSF010 MSF010
    ON TRIM(CTE.MAINT_ENG_CODE)=TRIM(MSF010.TABLE_CODE) AND TRIM(MSF010.TABLE_TYPE)='+ME' AND MSF010.ACTIVE_ROW_FLAG='Y'


    upload_2018-7-8_18-46-33.png



    however..

    if i do this without a cte and attempt on the maint_eng_code (SUBSTR(MSF010.ASSOC_REC, 3,6))
    it has nulls in the maint_eng_desc column...

    SELECT
    MSF720.WORK_GROUP, MSF720.WORK_GRP_DESC, MSF720.WORK_GROUP_SET,
    MSF010.TABLE_DESC AS WGST_DESC,
    SUBSTR(MSF010.ASSOC_REC, 3,6) AS MAINT_ENG_CODE,
    msf2.table_desc as MAINT_ENG_DESC

    FROM ADS_FOUNDATION.ADS_FND_msf720 MSF720
    LEFT JOIN ADS_FOUNDATION.ADS_FND_MSF010 MSF010 ON TRIM(MSF720.WORK_GROUP_SET)=TRIM(MSF010.TABLE_CODE) AND TRIM(MSF010.TABLE_TYPE)='WGST' AND MSF010.ACTIVE_ROW_FLAG='Y' AND TRIM(MSF010.TABLE_TYPE)='WGST'

    LEFT JOIN ADS_FOUNDATION.ADS_FND_MSF010 MSF2 ON SUBSTR(MSF010.ASSOC_REC, 3,6) =TRIM(MSF2.TABLE_CODE) AND TRIM(MSF2.TABLE_TYPE)='+ME' AND MSF2.ACTIVE_ROW_FLAG='Y'


    upload_2018-7-8_18-49-8.png

    What am I doing wrong? Its something to do with joining on the maint_eng_code which I'm not properly referencing?

    Thanks,

    Rob.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,610
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which table do you THINK the second left join is using? Look closely and discover which table it actually IS joining on.