Hey r397, I have run into another issue. Not sure if you're still subscribed to this thread or not, but here goes.
I'm trying to develop some pagination but I have been unable to get the total number of rows for my queries. The way they are grouped must have something to do with it. Here is my query:
SELECT COUNT( * ) AS total, o.occupation, t.topcategory, s.subcategory, p.perkid, p.clientid, p.businessid, p.enddate, b.businessname, p.title, p.description, p.hits, a.zip, p.status
FROM perks p
INNER JOIN occupations o ON o.occupationid = p.occupationid
INNER JOIN topcategories t ON t.topcategoryid = p.topcategoryid
INNER JOIN subcategories s ON s.subcategoryid = p.subcategoryid
INNER JOIN businesses b ON b.businessid = p.businessid
INNER JOIN addresses a ON a.businessid = p.businessid
IN ( 46220, 46240, 46226, 46205, 46250, 46218, 46208 )
GROUP BY p.perkid
I get three rows (as I expect for this particular query), but the total in each one is the weird part. The first row has a count of 2, the second a count of 1, the third a count of 2. The reason for this is that each business can have multiple addresses associated with it so each address is creating a repeated row if it falls in the zipcode list. So one business can be at two addresses that both fall in that zip list. Sorting by p.perkid allows the page results to display just fine, but I can't figure out how to just get the total number of rows (3 in this case). Any ideas? Anyone?