Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    20

    Unanswered: SQL Multiple Joins + Subquery

    Hello,

    I searched already through the web and the forum but could not find a solution for my issue. The issue is I have a query with several left and inner joins and I want to limit one of the joins with a subselect. But obviously this is not working. Here is the pseydocode of my query:

    Code:
    select * from FROM (((Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
    and Table2.Field2='01' and Table2.Field3=1) LEFT JOIN Table3 ON Table1.Field4 = Table3.Field4
    and Table3.Field5<=CURRENT DATE AND Table3.Field6>CURRENT DATE and
    Table3.Field7 = (select max(Field7) from Table4 where Table4.Field4 = Table1.Field4 AND
    Table4.Field5<=CURRENT DATE AND Table4.Field6>CURRENT DATE and Table4.Field7 in ('01','02','35')))
    LEFT JOIN Table5 ON Table3.Field8= Table5.Field1 and Table5.Field2='PE')
    Error message im am getting is: SQL0338N An on clause associated with a join operator or in a merge statement is not valid.

    But how can I limit one of my left joins with a bit more complex condition (as the subselect really seems to fail.)

    Thanks in advance for any help!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to format your code so it is easier to read. That is just a jumble and no one will give you much help. Also supply your DB2 version and OS.

    Andy

  3. #3
    Join Date
    Sep 2012
    Posts
    20
    Hello,

    Sorry for that, here now again the query (not in pseydocode any more) in a more formated way (I have no tool to format it, hope it is better now)

    Code:
    Select Employeegroup.* 
    FROM
    (
     (
      ( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
        ON Employeegroup.ID = Employeedetail.ID 
        and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
      LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY
      and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE
      and Address.ADCD =
            (select max(ADCD) from SRS.Address as Address2 where Address2.PRKEY = Employeegroup.PRKEY
            AND Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE
            and Address2.ADCD in ('01','02','35')))
    LEFT JOIN SRS.Lookuptable as Lookuptable_COUNTRY ON Address.ADDR_CNTRY = Lookuptable_COUNTRY.CODE
    and Lookuptable_COUNTRY.GROUPCODE='PE')
    The issue is as said the subquery with the select max()... thing, as since I entered this one, I get the below mentioned error.

    Reason why I need to enter it is because an Employeegroup can have more that one valid address (a mailing address, a registered address, and addtional mailing address, etc) and I want to show the valid one with the highest ADCD (internal number field in the database) value.

    Systeminfo:
    Database server = DB2/NT64 9.5.8


    Thanks,
    Martin

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You cannot use subselect in a join condition, I don't think. Instead, join ADDRESS with that subselect, something like:
    Code:
    ...
    LEFT JOIN SRS.Address AS Address 
       ON Employeegroup.PRKEY = Address.PRKEY
    inner join (
       select 
          Address2.PRKEY,
          max(ADCD) 
       from SRS.Address as Address2 
       where 
            Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE
            and Address2.ADCD in ('01','02','35')
       group by Address2.PRKEY
    ) address_max
       on 
          Address.PRKEY = Address_max.PRKEY
    I'm not sure I understand the semantics of your data correctly, but I hope you get the idea.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2012
    Posts
    20
    Thanks very much! This works as expected.

    Perfect

Posting Permissions

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