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 <[EMAIL PROTECTED]> 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
- [EMAIL PROTECTED]
- 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.
