[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Problem with simple LEFT JOIN in SQL Query

I have a left join that's not acting right.  My guess is I have made some stupid error, but I cannot tell.  First off, I have this initial query that produces 16 records:

SELECT a.IMITM, a.IMLITM, a.IMSTKT
    FROM F4101 a
            WHERE a.IMSTKT <> 'O'
            AND a.IMSTKT <> 'U'
            AND a.IMSRP6 = '20' AND a.IMSRP7 = '220' AND a.IMSRP0 = 'CX'
        ORDER BY a.IMLITM ASC

Now when I do a simple left join with another tables, I get 100 results!

with Branch As (SELECT IBITM, IBMULT, IBSAFE, IBROQI      FROM f4102)
    SELECT a.IMITM, a.IMSTKT, u.IBMULT, u.IBSAFE, u.IBROQI
    FROM F4101 a
      LEFT JOIN Branch u on TRIM(u.IBITM) = a.IMITM
            WHERE a.IMSTKT <> 'O'
            AND a.IMSTKT <> 'U'
            AND a.IMSRP6 = '20' AND a.IMSRP7 = '220' AND a.IMSRP0 = 'CX'
        ORDER BY a.IMLITM ASC

I have this query doing several other left joins with no problem; but this join in particular is giving me hell.  Any thoughts?  Thanks!
0
katiep23
Asked:
katiep23
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, if the "left" join gives you more results than the original table, that means that that additional table has more rows on the matching key field (here, IBITM = IMITM).
you will have to find out which of those "multiple" rows from the Branch/ f4102 table you want to get returned, in case there are several for the same IBITM value.
0
 
katiep23Author Commented:
Ugh, I'm so stupid.  I realized this right after I posted the question!
0
 
LowfatspreadCommented:
so try it as

with Branch
   As (SELECT distinct trim(IBITM) as IBITM, IBMULT, IBSAFE, IBROQI    
            FROM   f4102)
    SELECT a.IMITM, a.IMSTKT, u.IBMULT, u.IBSAFE, u.IBROQI
       FROM F4101 a
        LEFT JOIN Branch u
           on u.IBITM = a.IMITM
    WHERE a.IMSTKT <> 'O'
         AND a.IMSTKT <> 'U'
         AND a.IMSRP6 = '20'
         AND a.IMSRP7 = '220'
         AND a.IMSRP0 = 'CX'
     ORDER BY a.IMLITM ASC

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now