In the last episode (May 17), Gustavo Andrade said:
> select count(distinct membros.ID) as total_membros, count(distinct
> replays.ID) as total_replays, count(distinct downloads.ID) as
> total_downloads from membros,replays,downloads;
>
> if one of the tables have 0 records all the counts will turn to 0
> the count works only if all the tables have records
> how can i fix that?
That's a very inefficient query to start off with; it's generating
membros*replays*downloads records, then removing the dupes from each
column and counting what's left. If you're running mysql 4.1, this
query will return data instantly (one of the very few examples of where
subselects are much better than joins):
SELECT (SELECT count(*) FROM membros) AS total_membros,
(SELECT count(*) FROM replays) AS total_replays,
(SELECT count(*) FROM downloads) AS total_downloads;
If you're running 4.0 or older, you'll need to split it up into 4
queries:
SELECT @membros:=count(*) FROM membros;
SELECT @replays:=count(*) FROM replays;
SELECT @downloads:=count(*) FROM downloads;
SELECT @membros AS total_membros, @replays AS total_replays,
@downloads AS total_downloads;
Discard the results of the first 3 queries. If you're running 4.0, you
can join the first three queries into a single UNION query.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
