--- princeadamz <[EMAIL PROTECTED]> wrote: > I have two tables, and I'm joining them by LEFT JOIN to get counts > result. It is working well as needed. > > Here is my working syntax: > > $query="SELECT table1.field2, table1.field5, table1.field9, > table2.field1, table2.field2, > > table2.field3, count(table1.field2) AS Tcount > FROM table1 > LEFT JOIN table2 ON table1.field2 = table2.field2 > WHERE table1.field9 = '1' > GROUP BY table1.field2 > order by 'Tcount' DESC LIMIT 5"; > > The result I get is something like this: > > > Tcount table1.field2 > 17 Tsunami > 13 Acheh > > Now, I want to add another table just to get another field with the > same keyword - and I'm > > not going to count or do anything else. > > Here is the syntax which is not working as needed: > > $query="SELECT table1.field2, table1.field5, table1.field9, > table2.field1, table2.field2, > > table2.field3, table3.field1, count(table1.field2) AS Tcount > FROM table1 > LEFT JOIN table2 ON table1.field2 = table2.field2 > LEFT JOIN table3 ON table1.field2 = table3.field1 > WHERE table1.field9 = '1' > GROUP BY table1.field2 > order by 'Tcount' DESC LIMIT 5"; > > The unwanted result I get is something like this: > > > Tcount table1.field2 table3.field1 > 119 Tsunami Tsunami > 13 Acheh Acheh > > > My question is: How can this be happening? > Where those additional rows coming from? > What is the syntax should look like to make this 3 > tables working correctly as needed? > > > > Thank you in advance > > Adam
One of the desirable side effects of a left join is that rows will be listed even if the match does not occur in both of the two tables. If you want to restrict the listings to ones where the matching data exists in both tables, a more traditional join is deirable: SELECT A.col1, B.col2 FROM table1 A, table2 B WHERE A.key=B.key Also, you are applying the ON syntax with your join. You may also want to be aware of the USING syntax. When you get too many rows, there is often a missing WHERE clause or similar condition to relate one table to another. Not knowing the relationship between your tables (other than what can be inferred from your code) it is not possible to say if you have the best query to extract the data. James Community email addresses: Post message: [email protected] Subscribe: [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] List owner: [EMAIL PROTECTED] Shortcut URL to this page: http://groups.yahoo.com/group/php-list Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php-list/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
