SELECT count(*) FROM table_a a, table_b b WHERE a.pkey = b.fkey ...
With a regular join the count function returns the number of rows that satisfy the conditions. But this is very straightforward, can't imagine MySql needs an alternate construction for that, do you need a count of rows involved in an outer join perhaps? Then you need to use something like:
SELECT count(a.pkey), count(b.pkey) FROM table_a a, OUTER table_b b
WHERE a.pkey = b.fkey ...
Tyveleyn, some times a DISTINCT clause can filter the results (the sql generator generates DISCTINT if the user ask for it), this is the reason I need to count by several columns, I have found a solution using multiset:
select count(*) as rownum
from table(multiset(SELECT DISTINCT.....)) s1;
Then I can get the number of rows of any sql query using a multiset, or I am wrong, there should be any restriction in make a subquery for a multiset?
I don't know if the MULTISET datatype is appropriate in your case. It's a datatype for a collection column, that stores multiple values in one attribute and makes the table a kind of multidimensional.
What else you could do is perform your task with subqueries, like:
SELECT DISTINCT a.some_column,
(SELECT count(*) FROM table1 b
WHERE b.some_column = a.some_column),
(SELECT count(*) FROM table2 c
WHERE c.foreign_key = a.primary_key)
FROM table1 a
In this case the count of the rows in the subqueries are independent from the number of results in the main query.