List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:April 3 2007 9:01pm
Subject:Re: LEFT/RIGHT Joins not working
View as plain text  
a left join and a right join are 2 very distinct things...   It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you.  You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).

The description you gave of your results using RIGHT and LEFT
universally are  consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:

http://dev.mysql.com/doc/refman/5.1/en/join.html

 - michael

On 4/3/07, murthy gandikota <murthy_gandikota@stripped> wrote:
> 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.


-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.
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