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

Reply via email to