List:General Discussion« Previous MessageNext Message »
From:murthy gandikota Date:April 3 2007 10:03pm
Subject:Re: LEFT/RIGHT Joins not working
View as plain text  
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.
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