A couple of things here. The problem you are having has nothing to do with the double left join. The problem is occuring in the left join between table2 and table3. With the way you have your tables set up
select table2.id as test,count(table2.no),count(table3.no) from table3 left join table2 ON table2.id=table3.id group by test; will give you the result set | test | count(table2.no) | count(table3.no) | | 1 | 6 | 6 | Which is exactly what you are running into with the double join. In order to fix this you need to rethink the way you have your tables setup. Why is id 1 for every field in table2 and table3? The id column should be used to uniquely identify rows, thereby allowing you to do things like what you are currently attempting. What is the actual data that you are trying to represent and why are you doing it in this fashion? Why is table1 even a part of this select? If you were to change table2 and table3 so that id was a unique identifier like so table2 1 | 1 2 | 1 table3 1 | 1 2 | 1 3 | 1 You could get the result set you said you wanted 1 | 2 | 3 with something like select table2.id as test,count(table2.no),count(table3.no) from table3 left join table2 ON table2.id=table3.id group by table3.no; We currently don't have enough to go on, and I think part of the problem is the design of the tables themselves. Reply with some specifics to the actual problem... not your proposed solution, and you will probably be more likely to get help from someone on the list. Also, if you do change the id fields in table2 and table3 as I have suggested you can get your 1 | 2 | 3 return set from all three tables using this double join select table1.id as test,count(table2.no),count(table3.no) from table3 left join table2 using(id) left join table1 using(id) group by table3.no; Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com ----- Original Message ----- From: "Bas Jobsen" <[EMAIL PROTECTED]> To: "Rick Emery" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, October 02, 2001 4:59 PM Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > Hi Rick, > > > please show the REAL output from your query and what it should REALLY look > > like > > table1: > id|no > 1|1 > 2|1 > > table2: > id|no > 1|1 > 1|1 > > table3: > id|no > 1|1 > 1|1 > 1|1 > > using: > SELECT DISTINCT(table1.id) AS test, SUM(table2.no) AS tot, SUM(table3.no) > FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON > table2.id=table3.id GROUP BY test ORDER BY tot DESC > > gives: > test|tot|SUM(table3.no) > 1|6|6 > 2|0|0 > > and i want to have: > test|tot|SUM(table3.no) > 1|2|3 > 2|0|0 > > Tnx, > > Bas > > > > > > > ----- Original Message ----- > From: "Rick Emery" <[EMAIL PROTECTED]> > To: "'Bas Jobsen'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Tuesday, October 02, 2001 11:11 PM > Subject: RE: [PHP-DB] Howto make a double LEFT JOIN > > > > please show the REAL output from your query and what it should REALLY look > > like > > > > -----Original Message----- > > From: Bas Jobsen [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, October 02, 2001 3:38 PM > > To: Rick Emery; [EMAIL PROTECTED] > > Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id > LEFT > > > JOIN table3 ON table2.id=table3.id > > > > Well, oke now i have: > > table1: > > id|no > > 1|1 > > 2|1 > > > > table2: > > id|no > > 1|1 > > 1||1 > > > > table2: > > id|no > > 1|1 > > 1||1 > > 1||1 > > > > The result i want tot have is: > > id|sum(table2.no)|sum(table3.no)| > > 1|2|3 > > 2|0|0 > > my query: > > > > SELECT DISTINT( table1.id) AS test, SUM(table2.no) AS tot, SUM(table3.no > > FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON > > table2.id=table3.id GROUP BY test ORDER BY tot DESC > > > > which give my something like: > > id|5|5| > > 1|2|3 > > 2|0|0 > > :( > > > > > > > > > > > > > > ----- Original Message ----- > > From: "Rick Emery" <[EMAIL PROTECTED]> > > To: "'Bas Jobsen'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Tuesday, October 02, 2001 10:19 PM > > Subject: RE: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id > LEFT > > > JOIN table3 ON table2.id=table3.id > > > > > > -----Original Message----- > > > From: Bas Jobsen [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, October 02, 2001 3:13 PM > > > To: Rick Emery; [EMAIL PROTECTED] > > > Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > > > > Remove the parentheses > > > > SELECT table1.id FROM table1 LEFT JOIN table2 LEFT JOIN table3 ON > > > > table2.id=table3.id ON table1.id=table2.id > > > > > > I still got: > > > MySQL retourneerde: You have an error in your SQL syntax near 'LEFT JOIN > > > table3 ON > > > table2.id=table3.id ON table1.id=table2.id' at line 1 > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
