Will SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() do what you want? Regards,
Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -----Original Message----- > From: Les Fletcher [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 01, 2007 3:27 PM > To: mysql@lists.mysql.com > Subject: counting on a complex query > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
