From: Jason Trebilcock
Date: September 11 2009 12:22am
Subject: RE: Natural join problem
List-Archive: http://lists.mysql.com/mysql/218695
Message-Id: <4aa9985b.14045a0a.0935.0f50@mx.google.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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

> -----Original Message-----
> From: John Meyer [mailto:john.l.meyer@stripped]
> Sent: Thursday, September 10, 2009 6:48 PM
> To: mysql@stripped
> 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