Complex Mysql with 3 INNER JOINS

Databases
1

This is a Question for MySQL Gods here:

We have a 3 Tables:
1- Member tables: members
2- Articles by members: qaqs
3- Like this articles gotten: qavotes

We want to see what is the Total number of Likes that articles by a given member have received by a given member. We tried this SELECT with 3 INNER JOINS but is not working:

SELECT COUNT(pau.user_id) FROM qaqs AS aqq INNER JOIN members AS pau ON (aqq.user_id = pau.user_id) WHERE aqq.user_id = 999999 AND aqq.blocked = ‘no’ INNER JOIN qavotes AS aqv ON (aqq.id = aqv.qa_id) AND aqv.vote = ‘up’

Thanks.

2

Well, you’re counting the author user_id, so I’m not sure you want. You want to count the up votes, not the article author…You don’t actually need the member table unless you’re trying to pull information from it.

This should give you a list of articles and the number of likes for each article. Hopefully this will get you closer to what you need…

SELECT article.id
     , COUNT(v.*) 
  FROM qaqs AS article
 INNER JOIN qavotes AS vote ON article.id = vote.qa_id AND vote.vote = ‘up’
 WHERE article.user_id = 999999 
   AND article.blocked = ‘no’ 
 GROUP BY article.id
1 Like
3

Yes, you are right, We do not need the members Table.
So then how do we list:

SELECT article_id, total number of likes that this article has received FROM tables: Articles & Votes for Given member XYZ who posted these articles. Tried this but not working:

SELECT qaq.id, qaq.heade_line, qav.q_id, SELECT COUNT(qav.id) FROM qaq
INNER JOIN qav ON (qaq.id = qav.q_id) AND qav.vote = ‘up’
WHERE qaq.user_id = 999999
GROUP BY qav.q_id
ORDER BY qaq.id DESC

4

I’m not sure what you’re asking.

The query I gave you should have provided you a list of articles posted by user_id 999999 and the number of likes for each article,

Edit: I did notice an error in the query (I changed the aliases to make them more meaningful and messed the count one up)

SELECT article.id
     , COUNT(articlevote.id) -- should be able to use COUNT(articlevote.*) 
  FROM qaqs AS article
 INNER JOIN qavotes AS articlevote ON article.id = articlevote.qa_id AND articlevote.vote = ‘up’
 WHERE article.user_id = 999999 
   AND article.blocked = ‘no’ 
 GROUP BY article.id
5

no, you can’t count “all columns” ;o)

but you can (and should) use COUNT(*) because it’s an inner join

if it were a left outer join, you’d want to count some column from the right table

6

Dave, That does not work. But thanks anyway. This is too complicated a query. I think we will use combo of Php & Mysql to get the desired values. Thanks again.

7

Dave, I stand corrected. Your code does work. Error, well bad data, was that I was not including a data in the Select which was throwing of the results. So it is good :slight_smile:

1 Like
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.