MySql total row count in multiple tables?

[rob76]

Please could some one help me with this. I am trying to return the total number of rows from multiple tables in mysql but things don't seem to be working. I am using this

$totalrowcount = mysql_result(mysql_query("SELECT COUNT(*) FROM table1,table2,table3"),"0");

This does return a number but it is far higher than the true row count from the tables added together.

Is there a way of doing this?

Rob

[mrhappiness]

afaik there's no way of doing this in one single statement

if you have 3 rows, 4 rows and 5 rows in your tables mysql will return 345 = 60

[bad76]

Hi,
the WHERE clauses is your answer. It's not enough tells to mysql you want count n tables, you have to join the tables.

So try with:

SELECT COUNT(*)
FROM table1,table2,table3
WHERE table1.tab2id=table2.id AND table2.tab3id=table3.id

I hope this helps.

[edit]
... reading better may be you find count(table1)+count(table2)+count(table3). As tells mrhappiness, you can't with a single query, or better, with a simple query (I think you can do it with some JOIN...).
[/edit]

[mrhappiness]

but this works only if the tables have something to do with each other