Check for a unique value within a count, but get all results

Posted by pedalpete on Stack Overflow See other posts from Stack Overflow or by pedalpete
Published on 2010-05-30T01:22:30Z Indexed on 2010/05/30 1:32 UTC
Read the original article Hit count: 244

Filed under:

mysql-query

|

count

I'm trying to create a single query which, similar to stack overflow, will give me the number of votes, but also make sure that the currently viewing user can't upvote again if they've already upvoted.

my query currently looks like

SELECT cid, text, COUNT(votes.parentid)  FROM comments LEFT JOIN votes ON comments.cid=votes.parentid AND votes.type=3 WHERE comments.type=0 AND comments.parentid='$commentParentid' GROUP BY comments.cid

But I'm completely stumpted on how to add the check to see if the userid is in the votes table.

The other option is to add a seperate query where

SELECT COUNT(*) FROM votes WHERE userid='$userid' AND parentid='$commentParentid' AND type=3

I'm just realizing I'm so lost with this that I don't even really know what tags to provide.

© Stack Overflow or respective owner

Related posts about mysql-query

MySQL query returns different set of results on two identical databases

as seen on Stack Overflow - Search for 'Stack Overflow'
I exported a live MySQL database (running mysql 5.0.45) to a local copy (running mysql 5.1.33) with no errors upon import. There is a view in the database, that when executed locally, returns a different set of data than when executed remotely. It's returning 32 results instead of 63. When I execute… >>> More
What's wrong with my MySql query ?!

as seen on Stack Overflow - Search for 'Stack Overflow'
This is a query I am doing with mysql using PHP This is the query line <?php $query = "SELECT * FROM node WHERE type = 'student_report' AND uid = '{$uid}' LIMIT 1 ORDER BY created DESC"; ?> I get the following error You have an error in your SQL syntax; check the manual that corresponds… >>> More
Optimizing the MySQL Query Cache

as seen on Internet.com - Search for 'Internet.com'
MySQL's query cache is an impressive piece of engineering if sometimes misunderstood. Keeping it optimized and used efficiently can make a big difference in the overall throughput of your application, so it's worth taking a look under the hood, understanding it, and then keeping it tuned optimally. >>> More
mysql query using jdbc

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi all, I have the following table: Service_ID feedback 31 1 32 1 33 1 1 I have the sample code to find the sum: ResultSet res = st.executeQuery("SELECT Service_ID,SUM(consumer_feedback)… >>> More
index help for a MySQL query using greater-than operator and ORDER BY

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a table with at least a couple million rows and a schema of all integers that looks roughly like this: start stop first_user_id second_user_id The rows get pulled using the following queries: SELECT * FROM tbl_name WHERE stop >= M AND first_user_id=N AND second_user_id=N… >>> More

Related posts about count

why i7-860 is two times slower than E3-1220?

as seen on Server Fault - Search for 'Server Fault'
I have financical trading software. It decodes fast/fix messages. I'm running same binaries on two different machines on very similar set of data. Software receive "messages" and decodes them. The general rule - longer message takes more time to decode: i7-860, Windows 7: Debug 18:23:48.8047325… >>> More
Count(*) vs Count(1)

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference for SQL Server 2005 in performance? Or is this just a legacy habit that has been brought forward from days gone past? >>> More
How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi I have three tables Post ID Name 1 'Something' 2 'Something else' 3 'One more' Comment ID PostId ProfileID Comment 1 1 1 'Hi my name is' 2 2 2 'I like cakes' 3 3 3 'I hate cakes' Profile ID Approved 1 1 2 0 … >>> More
SQL COUNT of COUNT

as seen on Stack Overflow - Search for 'Stack Overflow'
I have some data I am querying. The table is composed of two columns - a unique ID, and a value. I would like to count the number of times each unique value appears (which can easily be done with a COUNT and GROUP BY), but I then want to be able to count that. So, I would like to see how many items… >>> More
Design by Contract with Microsoft .Net Code Contract

as seen on ASP.net Weblogs - Search for 'ASP.net Weblogs'
I have done some talks on different events and summits about Defensive Programming and Design by Contract, last time was at Cornerstone’s Developer Summit 2010. Next time will be at SweNug (Sweden .Net User Group). I decided to write a blog post about of some stuffs I was talking about. Users are… >>> More