--- 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/
 


Reply via email to