CODEWITHSUNDEEP
mysqljoin
Ilja
Ilja

Reputation: 46479

Can't figure out mysql query

I'm new to PHP and MySQL -> I'm not good yet. Today I came across the problem. I have a query that joins 2 tables 'votes' and 'stories' Here it is:

SELECT stories.*, SUM(votes.vote_value) as 'total_votes' 
FROM stories JOIN votes ON stories.id = votes.item_name WHERE stories.st_date >= DATE_SUB(NOW(), INTERVAL 32 DAY) 
GROUP BY stories.id 
ORDER BY total_votes ASC LIMIT 10

I need to modify it so it only selects information from 'stories' table where field showing = 1

A simple query would look like this:

SELECT * FROM stories WHERE showing = 1

But I have no idea how to implement it the first query where I join two databases.

Upvotes: 1

Views: 49

Answers (5)

Rifat
Rifat

Reputation: 7718

SELECT stories.*, SUM(votes.vote_value) as 'total_votes' 
FROM stories, votes 
WHERE stories.id = votes.item_name AND stories.showing = 1 AND stories.st_date >= DATE_SUB(NOW(), INTERVAL 32 DAY)
GROUP BY stories.id 
ORDER BY total_votes ASC LIMIT 10

Upvotes: 2

Max Małecki
Max Małecki

Reputation: 1702

Use the HAVING showing = 1 and it should fix your issue.

Upvotes: 1

Dau
Dau

Reputation: 8848

change your query to 

SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories 
JOIN votes ON stories.id = votes.item_name 
WHERE stories.showing = 1 
GROUP BY stories.id 
ORDER BY total_votes ASC LIMIT 10

Upvotes: 1

Andrew Jackman
Andrew Jackman

Reputation: 13966

Its no problem just to add it in, since it is the base table of the joins:

SELECT stories.*, SUM(votes.vote_value) as 'total_votes' 
FROM stories JOIN votes ON stories.id = votes.item_name $date 
WHERE stories.showing = 1
GROUP BY stories.id 
ORDER BY total_votes ASC LIMIT 10

Upvotes: 1

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29965

SELECT stories.*, SUM(votes.vote_value) as 'total_votes' 
FROM stories JOIN votes ON stories.id = votes.item_name $date 
WHERE showing=1
GROUP BY stories.id 
ORDER BY total_votes ASC LIMIT 10

Simply stick the where in there. Make sure to place it in the right order though.

Upvotes: 2

Related Questions