I have a nice little query that I can get the results from fine, but am
having problems figuring out the best way to get the count directly from
mysql. The query looks like the following:
SELECT t1.id, t1.col1, t2.id, t2.col1, t2.col2, t2.col3, t2.col4 FROM t1
JOIN t2 ON ( t2.id = t1.col1 ) WHERE ( ... lots of OR's and AND's on t1
... ) GROUP BY t1.col1 HAVING ( count(*) = t2.col2 ) ORDER BY t2.col3,
t2.col4;
There is a one-to-many relationship between t2 and t1 ( lots of entries
in t1 associated with an entry in t2 ). The group by is just collapsing
the t1 matches to get unique entries in t2 while the HAVING is then
cutting that result set down further based on some predefined criteria.
This gets the set of records that I want. The problem is that I also
want to be able to page through the records, but still know how many of
them are, hence wanting to be able to count the number of results in the
set. Right now I know of two options, one is to just run the query with
out any limits and count the records in my application. The other is
two do the following:
SELECT COUNT(*) from ( SELECT t1.id, t1.col1, t2.id, t2.col1, t2.col2,
t2.col3, t2.col4 FROM t1 JOIN t2 ON ( t2.id = t1.col1 ) WHERE ( ... lots
of OR's and AND's on t1 ... ) GROUP BY t1.col1 HAVING ( count(*) =
t2.col2 ) ORDER BY t2.col3, t2.col4 ) t3;
Not sure if this is an optimal way to do it or not. Any suggestions on
a better way of getting the count?
Les
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
