Many thanks for your input. Looks like the older version of mysql, preceding
5.0 has some problems. I've to upgrade mysql now. :-(
Thanks again.
Mogens Melander <[EMAIL PROTECTED]> wrote:
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 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.
> --
> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--- Scanned by M+ Guardian Extreme Messaging Firewall by Messaging Architects
---
---------------------------------
Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
