CODEWITHSUNDEEP
mysqlsql
dham84
dham84

Reputation: 37

SQL Query to get IDs of elements that have X number of counts in another table

I was wondering if you could give me a hand with an SQL Query. I have two tables: Designs and Votes. The designs have votes from the users, so the structure of the Votes table looks something like this:

What I need, is to display in a web page all the designs that have less than 50 votes, but I'm struggling with the query... could you give me a hand?

I'm trying something like this, and it does return the Designs with less than 50 votes, however it does not include the designs with no votes at all.

    SELECT * FROM `votes` as Vote, `designs` as Design, `users` as User
WHERE `Design`.`id` = `Vote`.`design_id`
GROUP BY `Vote`.`design_id`
HAVING COUNT(*) > 50;

Needless to say I am a complete amateur in SQL.. Any help would be much appreciated.

Thanks guys!

Upvotes: 1

Views: 143

Answers (1)

Karl Lorey
Karl Lorey

Reputation: 1616

SELECT designs.*, photo.* /* You only need designs */
FROM designs /* You are looking for designs, so start there */
JOIN photos ON designs.id = photos.design_id /* append the matching photo */
LEFT JOIN votes ON designs.id = votes.design_id /* append all votes */
GROUP BY designs.id /* group all rows by design */
HAVING COUNT(*) > 50;

What I am doing here: I select all designs and append the matching photo (JOIN because there is always one). Next I select all matching votes. You have to use LEFT JOIN to also select projects with no matching vote (JOIN would leave them out, try it without GROUP BY to grasp the difference). Then I GROUP BY design and can count by HAVING.

Note that this join because of the GROUP BY statement would get you a random user because there are several votes and therefore several users per design. So I left it out. Why do you need it? (Just leave me a comment on what you're trying to do)

For those wondering about photos, take a look at the comments:

Each design has a photo and each vote comes from a user. So table photos has a "desing_id" and Desing has "photo_id". Then vote has "user_id" and Design has "vote_id"

To learn more about joins:

Upvotes: 2

Related Questions