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!
katiep23Asked:
Who is Participating?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.