Thanks, this is very helpful. I'm finally starting to understand
Joins. But now I have a serious performance problem. Using INNER JOIN...
SELECT users.*, COUNT(history.user_id) as num_events
FROM users
INNER JOIN history ON (users.id = history.user_id)
GROUP BY users.user_name
ORDER BY id DESC
This is FAST, but incomplete—doesn't include users without events
("0" events in history table). 0.1 seconds to fetch 7 rows. Using
LEFT JOIN
SELECT users.*, COUNT(history.user_id) as num_events
FROM users
INNER JOIN history ON (users.id = history.user_id)
GROUP BY users.user_name
ORDER BY id DESC
This is COMPLETE, but slow—includes users even with zero events, but
takes 18 seconds to fetch 62 rows. (Incidentally, the history table
is big, about 15000 records).
So... I would love to see the performance of the Inner Join, but with
the all users fetched (even those with zero history events), that is
what Left Join gives me.
Any more ideas?
...Rene
On 29-Jul-05, at 6:01 PM, Ed Reed wrote:
select USERS.Name, Count(WINS.user_id)
From USERS inner join WINS on WINS.user_id = USERS.id
Group By USERS.Name
René Fournier <[EMAIL PROTECTED]> 7/29/05 4:40 PM >>>
Let's say I have two tables:
USERS
id name
1 John
2 Mary
3 Sue
4 Paul
5 David
WINS
id user_id
1 2
2 4
3 3
4 5
5 1
6 4
7 4
8 2
9 3
10 1
How canin one SELECT statementfetch and display all the users,
along with the number of games they each one, e.g.:
User Games Won
----------------
John 2
Mary 2
Sue 2
Paul 3
David 1
Is this a job for Subselects?
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
