List:General Discussion« Previous MessageNext Message »
From:murthy gandikota Date:April 3 2007 8:31pm
Subject:LEFT/RIGHT Joins not working
View as plain text  
I tried the following 2 SQL's and the results are less than satisfactory. The RIGHT join
does not show where disposition is NULL. The LEFT join shows dispositions as NULL where
they shouldn't be. Also the LEFT join generates more dupes. Any way to fix this?
   
  select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as
CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as
DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE,
cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN
disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment > 0 and cust.disposition
> 0 order by SOURCE, DISPOSITION
   
  select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as
CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as
DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE,
cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN
disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment > 0 and cust.disposition
> 0 order by SOURCE, DISPOSITION
   
  The MYSQL has the following version
   
  mysql  Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
   
  Thanks for your help
  Murthy

 
---------------------------------
Don't get soaked.  Take a quick peek at the forecast 
 with theYahoo! Search weather shortcut.
Thread
Joins versus Grouping/Indexing: Normalization Excessive?Daniel Cousineau3 Apr
  • Re: Joins versus Grouping/Indexing: Normalization Excessive?Micah Stevens3 Apr
  • RE: Joins versus Grouping/Indexing: Normalization Excessive?Jerry Schwartz3 Apr
    • LEFT/RIGHT Joins not workingmurthy gandikota3 Apr
      • Re: LEFT/RIGHT Joins not workingMichael Dykman3 Apr
        • Re: LEFT/RIGHT Joins not workingmurthy gandikota3 Apr
          • Re: LEFT/RIGHT Joins not workingMogens Melander4 Apr
            • Re: LEFT/RIGHT Joins not workingmurthy gandikota4 Apr