Thank you for the reply James, maybe I should explain myself better. Yes, I
actually want all in table1.field2 to be listed, group by, and counted -
whether it is in table2, table3 or not.
From two remining talbes, table2 and table3: all I want is to select 1 field
from each.
I dont see any reason y to use regural join if I want all in table1 to be
listed.
Thanx
James Keeline <[EMAIL PROTECTED]> wrote:
--- 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
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
[Non-text portions of this message have been removed]
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/
