List:General Discussion« Previous MessageNext Message »
From:Les Fletcher Date:August 1 2007 7:26pm
Subject:counting on a complex query
View as plain text  
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
Thread
counting on a complex queryLes Fletcher1 Aug
  • RE: counting on a complex queryJerry Schwartz1 Aug
    • Re: counting on a complex queryPerrin Harkins1 Aug
      • Re: counting on a complex queryLes Fletcher2 Aug
        • Re: counting on a complex queryPerrin Harkins2 Aug