Problem with Count when selecting from multiple tables.

WeberForums.com Forum Index -> MySQL General

View previous topic :: View next topic   Author Message JonathonReinhart Joined: 06 Feb 2008 Posts: 1 Location: Ohio Posted: Wed Feb 06, 2008 2:25 am    Post subject: Problem with Count when selecting from multiple tables. Hello. I currently am working on developing an application. For reference, the related mysql tables look like this (slimmed down to applicable fields): `users` --------------------------------------- `id` int unsigned `privacy` enum('EVERYONE','FRIENDS') `quotes_tags` --------------------------------------- `qt_id` int unsigned `subject` int unsigned Subject is a foreign key, `id` on `users`. I am trying to get the user id (same as subject) of the 5 people who have the most entries in `quotes_tags`. In the same query, I am trying to also get that user's `privacy` entry. And here is the current query that I am currently using: Code: SELECT    quotes_tags.subject,    users.privacy_setting AS privacy, COUNT(quotes_tags.subject) AS count FROM `quotes_tags`, `users` GROUP BY subject ORDER BY count DESC LIMIT 0, 5 And that will return a dataset that looks something like: Code: +-----------+----------+-------+ | subject   | privacy  | count | +-----------+----------+-------+ |         2 | EVERYONE |     8 | |         1 | EVERYONE |     4 | | 568623702 | EVERYONE |     4 | +-----------+----------+-------+ However, the data is incorrect. 568623702's privacy is FRIENDS, and all of the counts are wrong. If you can see what I'm trying to do, please help me get my query straightened up. If you are confused about what I'm trying to do, I will try to explain more, or create a simplified case with simple data. Thanks a lot! Jonathon[/code] Back to top lostboy Joined: 02 May 2004 Posts: 5574 Location: toronto, canada Posted: Thu Feb 07, 2008 5:50 am    Post subject: SELECT quotes_tags.subject, users.privacy_setting AS privacy, COUNT(quotes_tags.subject) AS count FROM `quotes_tags`, `users` GROUP BY subject ORDER BY count DESC LIMIT 0, 5 I notice that you are referencing privacy_setting as another field, that may be part of the problem... _________________ Lostboy Cat, the other other white meat Please read Posting Etiquette before posting You can always try Google Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

WeberTrivia Questions   Think you are smart? Prove it!. Try your skills with these questions :   Recursive arrays and multi-dimensional arrays are one and the same. (PHP and MySQL)   The \"cache_dir\" tag of the squid configuration has a default of /var/spool/squid. (Linux)

	WeberForums.com Forum Index -> MySQL General	All times are GMT + 2 Hours
Page 1 of 1

Jump to: Select a forum PHP Forums----------------PHP GeneralPHP MySQL Jobs and ProjectsPHP Articles Tutorials and ScriptsWhy Use PHPPEARPHP Books MySQL Forums----------------MySQL General Apache Forums----------------Apache General Web Development Forums----------------Java Script / AJAXSearch Engine Optimization (SEO)Cascading Style SheetsHTMLXML & XSL Graphics & Design----------------Theory related web designWeb Design Security Forums----------------Security Linux Forums----------------Linux General Web Development Misc----------------Hosting Related Miscellaneous----------------Feedback & TestimonialsMisc  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum