ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Outer join sql/400

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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.

    Comment


    • #3
      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 23, 2009, 01:34 AM.

      Comment


      • #4
        Re: Outer join sql/400

        Did you get an error?
        If so which one?

        Comment


        • #5
          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 [COLOR="Red"]LEFT[/COLOR] 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.

          Comment


          • #6
            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

            Comment


            • #7
              Re: Outer join sql/400

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

              Birgitta

              Comment


              • #8
                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.

                Comment

                Working...
                X