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.
If I remove the GROUP BY part of the query then i can get a single
result...this is what i want, HOWEVER, the count doesn't match the number of
rows in the original query. that one event that has 4 subcategories
associated with is counted once for each subcategory association which means
my count is too high be the extra 3 records. the 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
)
)
===
returns this:
count
14
--
View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6827498
Sent from the Php - Database mailing list archive at Nabble.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
