Natural join problem

Two tables:

USERS:
USER_ID (PK)
.. . .etc

TWEETS:
TWEET_ID (PK)
USER_ID (FK)

Trying to get the user information and the number of tweets each person has:

SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM
TWEETS NATURAL JOIN USERS;

But it seems to be just rolling up all the information into one row.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Natural join problem

Methinx you need a "GROUP BY" in there. See below.

> -----Original Message-----
> From: John Meyer [mailto:john.l.meyer@gmail.com]
> Sent: Thursday, September 10, 2009 6:48 PM
> To: mysql@lists.mysql.com
> Subject: Natural join problem
>
> Two tables:
>
> USERS:
> USER_ID (PK)
> . . .etc
>
> TWEETS:
> TWEET_ID (PK)
> USER_ID (FK)
>
> Trying to get the user information and the number of tweets each person
> has:
>
> SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM
> TWEETS NATURAL JOIN USERS;
>

select u.user_name, count(t.tweet_id)
from users u, tweets t
where u.user_id = t.user_id
group by u.user_name


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Natural join problem

Thanks. That worked.

Jason Trebilcock wrote:
> Methinx you need a "GROUP BY" in there. See below.
>
>
>> -----Original Message-----
>> From: John Meyer [mailto:john.l.meyer@gmail.com]
>> Sent: Thursday, September 10, 2009 6:48 PM
>> To: mysql@lists.mysql.com
>> Subject: Natural join problem
>>
>> Two tables:
>>
>> USERS:
>> USER_ID (PK)
>> . . .etc
>>
>> TWEETS:
>> TWEET_ID (PK)
>> USER_ID (FK)
>>
>> Trying to get the user information and the number of tweets each person
>> has:
>>
>> SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM
>> TWEETS NATURAL JOIN USERS;
>>
>>
>
> select u.user_name, count(t.tweet_id)
> from users u, tweets t
> where u.user_id = t.user_id
> group by u.user_name
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org