7 Replies Latest reply: Aug 14, 2014 1:03 PM by AVIRAL NAG RSS

    Clarification on Left Joins

    Jerome Jackson

      TABLE1:

      Load

      [Loan Number],

      [Customer],

      [Customer Invoice Number]

       

      from [qvd\workspace\name.qvd].(qvd)

      where match([Customer Invoice Number]>0;

       

      TABLE2:

      Load

      [Loan Number],

      [Maturity Date]

       

      from [qvd\workspace\name2.qvd].(qvd);

       

      I want to do a left join.  When I insert left join right before TABLE2 I get a table not found.  Essentially I have to create TABLE2 first.  This create two table in the table viewer and I get unwanted data (ie records with no Customer Invoice ID).  What would be the proper way to get a left join (all records in TABLE1 and matching records in TABLE2).  Essentially would I not be bringing together two resident tables as a left join

        • Re: Clarification on Left Joins
          Simen Kind Gulbrandsen

          TABLE1:

          Load

          [Loan Number],

          [Customer],

          [Customer Invoice Number]

          from [qvd\workspace\name.qvd] (qvd);

          /*where match([Customer Invoice Number]>0; this syntax is not correct and you have to change your where clause*/

           

          left join(TABLE1)

          TABLE2:

          Load

          [Loan Number],

          [Maturity Date]

           

          from [qvd\workspace\name2.qvd] (qvd);

          • Re: Clarification on Left Joins
            AVIRAL NAG

            Hi

             

            First try to understand how Left Join works:

             

            Suppose you have 2 tables:

             

             

            Load *,

            From Table1

            Left Join(Table1)

            Load *,

            From Table2

             

            It will give you below result:

             

             

            Try this:

             

            TABLE1:

            Load

            [Loan Number],

            [Customer],

            [Customer Invoice Number]

            from [qvd\workspace\name.qvd].(qvd)

            where [Customer Invoice Number]>0;

             

            LEFT JOIN(TABLE1)

            Load

            [Loan Number],

            [Maturity Date]

            from [qvd\workspace\name2.qvd].(qvd);

             

             

            Other thing, you need to check that which table you have to place above Table2 or Table1 depending upon your requirement.

             

             

            Regards

            Aviral Nag

              • Re: Clarification on Left Joins
                Jerome Jackson

                Let me make sure I understand you.  Are you saying create TABLE1 then do a left join to the same TABLE 1 then immediately load TABLE2 ?? Did you actually mean  left join TABLE2.

                  • Re: Clarification on Left Joins
                    Michael Gardner

                    Join takes an [optional parameter] to specify which table to Join to.  If the optional parameter is left blank it joins to the table that was loaded immediately before it.

                     

                    So when he typed JOIN(TABLE1) he is specifying to Join  TABLE2 to TABLE1.  If he had typed JOIN without the parameter  it would have done the same implied.

                    • Re: Clarification on Left Joins
                      AVIRAL NAG

                      You have not understood the concept yet.

                       

                      See this:

                       

                      Left Join Clarification.bmp

                       

                      I think, you are confusing between LEFT JOIN(TABLE1) syntax. It is not necessary to write TABLE1 along with LEFT JOIN. You can also Left Join using this:

                       

                      TABLE1:

                      Load

                      [Loan Number],

                      [Customer],

                      [Customer Invoice Number]

                      from [qvd\workspace\name.qvd].(qvd)

                      where [Customer Invoice Number]>0;

                       

                      LEFT JOIN

                       

                      Load

                      [Loan Number],

                      [Maturity Date]

                      from [qvd\workspace\name2.qvd].(qvd);

                       

                       

                      Sometimes there are Tables with similar fields. So, in order to avoid the Joining between wrong tables, we specify Table Name for correct Joining. You can use this approach:

                       

                      TABLE1:

                      Load

                      [Loan Number],

                      [Customer],

                      [Customer Invoice Number]

                      from [qvd\workspace\name.qvd].(qvd)

                      where [Customer Invoice Number]>0;

                       

                      LEFT JOIN

                      Load

                      [Loan Number],

                      [Maturity Date]

                      from [qvd\workspace\name2.qvd].(qvd);

                       

                       

                       

                       

                      Hope that clarify your doubts.

                       

                      Regards

                      Aviral