Results 1 to 8 of 8

Thread: Outer join sql/400

  1. #1
    Experienced Forum Member
    Join Date
    Jul 2007
    Location
    VN
    Posts
    130
    Rep Power
    655

    Outer join sql/400

    Hi All

    I can not user outer join in sql/400 , does any one help me to do?

    Example

    Code:
    
    T1
    
    A     200
    
    B     300
    
    C     500
    
    
    
    T2
    
    A     X
    
    C     Y
    
    Result should be :
    
    A     200            X
    
    B     300           
    
    C     500           Y

    Thank you so much.

    Tintin

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Admin kitvb1's Avatar
    Join Date
    Aug 2007
    Location
    Stammbach, Germany
    Posts
    2,281
    Rep Power
    3860

    Re: Outer join sql/400

    You can not... or don't know how?
    You might want to post your code... at least what you've got so far.
    Regards

    Kit
    http://www.ecofitonline.com
    DeskfIT - ChangefIT - XrefIT
    ___________________________________
    There are only 3 kinds of people -
    Those that can count and those that can't.

  4. #3
    Experienced Forum Member
    Join Date
    Jul 2007
    Location
    VN
    Posts
    130
    Rep Power
    655

    Re: Outer join sql/400

    Yes,I do not know, could you help me? Thank you



    SELECT a.POL,a.DAT1 , a.DATE2,a.SEL0004 ,b.Total,
    b.SEL0005 FROM test/g1 a outer join test/l1 b on
    A.POL=b.POL

    Tintin
    Last edited by tintin; November 23rd, 2009 at 02:34 AM.

  5. #4
    Analyst
    Join Date
    Sep 2005
    Location
    Germany
    Posts
    1,579
    Rep Power
    9518

    Re: Outer join sql/400

    Did you get an error?
    If so which one?

  6. #5
    Admin kitvb1's Avatar
    Join Date
    Aug 2007
    Location
    Stammbach, Germany
    Posts
    2,281
    Rep Power
    3860

    Re: Outer join sql/400

    This should work:
    Code:
    SELECT a.POL, a.DAT1, a.DATE2, a.SEL0004, b.Total, b.SEL0005 
    FROM test/g1 a LEFT outer join test/l1 b 
    on A.POL=b.POL
    Regards

    Kit
    http://www.ecofitonline.com
    DeskfIT - ChangefIT - XrefIT
    ___________________________________
    There are only 3 kinds of people -
    Those that can count and those that can't.

  7. #6
    Analyst BenThurley's Avatar
    Join Date
    Dec 2006
    Location
    Southampton (UK)
    Age
    32
    Posts
    818
    Rep Power
    2523

    Re: Outer join sql/400

    The question asked for an outer join, as in a full outer join. Doing a left outer join is exactly the same as a left join. To clarify there are several different basic types of join.

    Inner join, join:
    Returns a row where there is a match in both tables.

    Left join, left outer join:
    Returns all of the records from the table on the left side of the join statement and those records from the right table that match on the join condition.

    Right join, right outer join:
    Is the reverse of the left join.

    Outer join, full outer join:
    Returns all records from both tables. Joins rows where the keys match, where they don't you get nulls on one side and data on the other side of the join.

    In the example given your left join would work. However, if the 'B' row was in t2 rather than t1 it would not have been returned as it should.

    Full outer joins are possible in enterprise database systems like Oracle, SQL server and DB2 (the proper version). However, it is not standardised SQL and you can't do it in trimmed down databases like MySQL or DB2 (for the AS400).

    I needed to do a full outer join myself recently and managed it with a kind of workaround. You have to think what a full outer join effectively is. The left join example above nearly works but it is missing those records from the right hand side that do not have a match on the left hand side. You can get these records with a right exception join. this has to be done with a second select statement and the results unioned.

    Using a union is a bit of a pain because the two select statements must return identical fields. I got round this by using the coalesce function in my select statement to kind of merge values.

    Be warned, the SQL is pretty horrible and in the end I couldn't get it to perform on a full scale database, about 9 million records. That's not particularly big by today's standards but it's a very old database so the design is a bit quirky.

    IBM have an article with example syntax.

    The links on their site always break so here's the SQL. I just modified it for my own needs.
    PHP Code:
    SELECT EMPNOLASTNAMEPROJNO
         FROM CORPDATA
    .EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT
               ON EMPNO 
    RESPEMP
         WHERE LASTNAME 
    'S'
      
    UNION
      
    (SELECT EMPNOLASTNAMEPROJNO
         FROM CORPDATA
    .PROJECT EXCEPTION JOIN CORPDATA.EMPLOYEE
               ON EMPNO 
    RESPEMP
         WHERE LASTNAME 
    'S'); 
    Ben

  8. #7
    Analyst
    Join Date
    Sep 2005
    Location
    Germany
    Posts
    1,579
    Rep Power
    9518

    Re: Outer join sql/400

    Full outer join is introduced with Release 6.1 on the i!

    Birgitta

  9. #8
    Experienced Forum Member
    Join Date
    Jul 2007
    Location
    VN
    Posts
    130
    Rep Power
    655

    Re: Outer join sql/400

    HI All


    Thank you so much for your supporting, My result has worked so nice. From Ben 's explaination I have more understand about all types of join.

    Thank you , tintin.

  10. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. Order By with Case
    By FaStOnE in forum SQL
    Replies: 30
    Last Post: March 18th, 2011, 09:00 AM
  2. Replies: 3
    Last Post: September 8th, 2009, 06:41 AM
  3. Condition outer join
    By snufse in forum SQL
    Replies: 18
    Last Post: December 12th, 2008, 11:38 AM
  4. left outer join
    By rx_b10 in forum SQL
    Replies: 1
    Last Post: January 24th, 2008, 07:01 AM
  5. Replies: 2
    Last Post: May 11th, 2007, 06:25 PM

Bookmarks

Posting Permissions

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