sneakyimp wrote:
The original query results (minus most of the fields but including the
COUNT(esa.id) part) would look something like this:
id title subcat_count
60 Another Halloween Party 4
50 Satan's Midnight October Bash 1
61 Halloween IPN Testing party 1
19 test 1
64 I happen more than once today 1
64 I happen more than once today 1
64 I happen more than once today 1
64 I happen more than once today 1
64 I happen more than once today 1
64 I happen more than once today 1
64 I happen more than once today 1
If I adjust the query, using only a COUNT(*) in the select part and leaving
the GROUP BY stuff (which is necessary to avoid counting each ESA table
association more than once) then I get a query like this:
===
SELECT COUNT(*) AS count
FROM demo_event_time_assoc eta,
demo_events e,
demo_event_subcategory_assoc esa,
demo_zip_codes z
WHERE eta.event_id=e.id
AND esa.event_id=e.id
AND z.zip=e.zip
AND e.active=1
AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
AND (
(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <=
1162368000)
OR
(eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
GROUP BY eta.id
===
which gives me this as a result:
count
1
4 <-- for this particular e.id, there are multiple entries in the ESA table
1
1
1
1
1
1
1
1
1
NOTE: there is one row in this result for each of the original rows. so
the ROWCOUNT is the same and still correct at 11 rows. Ultimately, what I
want is a query that returns ONLY THE ROW COUNT OF THE ORIGINAL QUERY. In
this case, 11.
What mysql version are you using? I wonder if you can use this part as a
subquery and then:
select sum(foo) as "total" from (SELECT COUNT(*) AS count
FROM demo_event_time_assoc eta,
demo_events e,
demo_event_subcategory_assoc esa,
demo_zip_codes z
WHERE eta.event_id=e.id
AND esa.event_id=e.id
AND z.zip=e.zip
AND e.active=1
AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
AND (
(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <=
1162368000)
OR
(eta.end_timestamp < 1162281600 AND eta.end_timestamp >
1162285200))
GROUP BY eta.id) AS foo;
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
