List:General Discussion« Previous MessageNext Message »
From:Mogens Melander Date:April 3 2007 11:15pm
Subject:Re: LEFT/RIGHT Joins not working
View as plain text  
Well, trying your example gives me the expected result.

select cust.ssn, disposition.disposition, source.source
from cust LEFT JOIN source ON (cust.source=source.id)
LEFT JOIN disposition ON (cust.disposition=disposition.id);

'123456789', 'dispo2', 'source1'
'123456780', 'dispo1', 'source2'
'123456781', null, null

On Wed, April 4, 2007 00:03, murthy gandikota wrote:
> Hi Mike
>   Thanks for your input. I read the page before and it caused more
> confusion.
>   May be an example would clarify this:
>
>   create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
> int(3));
>   insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
>   insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
>   insert into cust (ssn, source, disposition) values ('123456781', NULL,
> NULL);
>
>   create table `source` (`id` int(3), `source` varchar(10));
>   insert into source(id, source) values (1, 'source1');
>   insert into source(id,source) values (2, 'source2');
>
>   create table `disposition` (`id` int(3), `disposition` varchar(10));
>   insert into disposition (id, disposition) values (1, 'dispo1');
>   insert into disposition(id,disposition) values (2, 'dispo2');
>
>   Now I run the sql:
>   select cust.ssn, disposition.disposition, source.source from cust LEFT
> JOIN source ON (cust.source=source.id) LEFT JOIN disposition ON
> (cust.disposition=disposition.id)
>
>   +-----------+-------------+---------+
> | ssn       | disposition | source  |
> +-----------+-------------+---------+
> | 123456789 | dispo2      | source1 |
> | 123456789 | dispo2      | source1 |
> | 123456780 | dispo1      | source2 |
> | 123456780 | dispo1      | source2 |
> | 123456781 | NULL        | NULL    |
> +-----------+-------------+---------+
>
>   I don't want this cos the ssn 123456780 is shown twice
>
>   select cust.ssn, disposition.disposition, source.source from cust RIGHT
> JOIN source ON (cust.source=source.id) RIGHT JOIN disposition ON
> (cust.disposition=disposition.id)
>
>    +-----------+-------------+---------+
> | ssn       | disposition | source  |
> +-----------+-------------+---------+
> | 123456789 | dispo2      | source1 |
> | 123456789 | dispo2      | source1 |
> | 123456780 | dispo1      | source2 |
> | 123456780 | dispo1      | source2 |
> +-----------+-------------+---------+
>
>   This has the same problem.
>
>   All I want is
>
>   +-----------+-------------+---------+
> | ssn       | disposition | source  |
> +-----------+-------------+---------+
> | 123456789 | dispo2      | source1 |
> | 123456780 | dispo1      | source2 |
> | 123456781 | NULL        | NULL    |
> +-----------+-------------+---------+
>
>   I'd appreciate your help.
>   Thanks
>   Murthy
> Michael Dykman <mdykman@stripped> wrote:
>   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 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.
>
>
>
> ---------------------------------
> 8:00? 8:25? 8:40?  Find a flick in no time
>  with theYahoo! Search movie showtime shortcut.
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>


-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

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