Current time: 04-24-2014, 12:47 PM Hello There, Guest! (LoginRegister)

Post Reply 
MySQL Count(*) across multiple tables
03-06-2004, 01:37 AM
Post: #1
MySQL Count(*) across multiple tables
i have been messing around for over four hours and STILL can't get my code to do what i want it to do.

I'm trying to count the number of posts given by a user across multiple tables.

So far i have:

SELECT COUNT(*) AS Total FROM forumone,
forumtwo, forumthree WHERE forumone.name = '$name' AND forumtwo.name = '$name' AND forumthree.name = '$name'";

Now i'm getting a number, some number, but it's nowhere NEAR the number i think it should be. ha.

any help would be greatly appreciated.
Find all posts by this user
Quote this message in a reply
03-06-2004, 11:00 AM
Post: #2
MySQL Count(*) across multiple tables
First up, I don't think you can do this in MySQL in a single query. I have come up with some general ANSI SQL 92 solutions, tested in MS SQL Server 2000, but MySQL's subset of SQL - even in version 4 - continues to surprise me with its limitations.

Let me try to show you what's wrong with your solution. I've set up some very simple tables - just two, as it's easier to explain.

Create Table forumone (id int, name varchar(30));
Insert Into forumone values (1, 'foo');
Insert Into forumone values (2, 'bar');
Insert Into forumone values (3, 'foo');

Create Table forumtwo (id int, name varchar(30));
Insert Into forumtwo values (1, 'bar');
Insert Into forumtwo values (2, 'foo');
Insert Into forumtwo values (3, 'bar');

You can Cross Join the tables like this, to see all the combinations:

Select *
From forumone, forumtwo;

(Of course you can also do "Select * From forumone Cross Join forumtwo;".)

But in your query, you created an Inner Join, matching up on the name column. Or in other words, you filtered all those row combinations except those where name matches:

Select *
From forumone, forumtwo
Where forumone.name = 'foo' And forumtwo.name = 'foo';

...which brings us to the results you achieved: a count of all the *matching* rows, probably not what you want:

Select Count(*)
From forumone, forumtwo
Where forumone.name = 'foo' And forumtwo.name = 'foo'

Here's a way to count all posts where name = 'foo' across all forums:

Select Count(*)
From
(
Select name
From forumone
Union All
Select name
From forumtwo
) As A
Where name = 'foo';

This uses a derived table, the A alias can be anything. Unfortunately, MySQL doesn't support derived tables yet.

Another solution is to use an outer join:

Select Count(*)
From forumone as f1
Full Outer Join forumtwo as f2 On 0 = 1
Where f1.name = 'foo' or f2.name = 'foo';

But this one surprised me actually - MySQL doesn't support outer joins.

Going back to the derived table solution above, a derived table is really just a way of using a view without acutally creating the view. MySQL *does* support Union:

Select name
From forumone
Union All
Select name
From forumtwo;

- so surely all I need do is wrap it up in a View, and query that?

Create View forumsall
As
Select name
From forumone
Union All
Select name
From forumtwo;

- and then query it like so:

Select Count(*)
From forumsall
Where name = 'foo';

But yep, you gussed it. MySQL doesn't have views yet.

My advice: treat MySQL for what it is - a handy repository for data, not a fully fledged RDBMS. Query each table in turn in your host language, e.g. PHP, and add the results. If you must keep it pure SQL, something like this should work:

Create Temporary Table forumsum (forum varchar(30), total int);
Insert Into forumsum Select count(*) From forumone Where name = 'foo';
Insert Into forumsum Select 'forumtwo', count(*) From forumtwo Where name = 'foo';
Drop table forumsum;

You don't need the forum column, but it might be handy for debugging.

I'd be interested to know if anyone *does* have a better solution for MySQL 4.0.x however.
Find all posts by this user
Quote this message in a reply
03-06-2004, 09:21 PM
Post: #3
MySQL Count(*) across multiple tables
See, this is exactly why i hated conueries. how was i supposed to keep down my queries when the language doesn't even do what i need it to do! ha

there really HAS to be a better way, doesn't there? i already figured i could add them together, i just didn't want to "waste" queries. this seems like it should be a simple enough task.

well if anyone figures it out, please let me know.
Find all posts by this user
Quote this message in a reply
03-07-2004, 02:17 AM
Post: #4
MySQL Count(*) across multiple tables
I wonder, in what context are you wanting to run these queries? Do you need to just query an individual user's posts on a user profile page, or are you trying to create a post count next to every message? Or is it just for producing an overall report, perhaps daily?

You shouldn't need to loop through in PHP querying once for each user. You can count the posts for all users in one query (or three queries in the case where you have three tables), for example something like:

Select name, count(*) as num_posts
From forumone
Group by name

Or for a subset of users:

Select name, count(*) as num_posts
From forumone
Where name in ('user1', 'user2', 'user3')
Group by name

You can use Union All to do all this in one query, save the results into a temp table, and then do the same count(*) and Group By on that.

But counting is relatively expensive, you probably wouldn't want to do that when displaying each page/each post. Better to have a num_posts column in the users table and increase each user's post count when they post. That's what phpBB does.
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump: