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 <[EMAIL PROTECTED]> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
