Left outer join and count

Databases
1

In my first table, I have the articles, ordered by a unique id, and in the second, I have the visits that those articles receive.

I need to make a SQL syntax that shows me the most readed articles in the past month.

How can I do that? Using Left Outer Join?

Thank you!

2

by “past month” do you mean march 2010, or do you mean the previous 30 days from today?

yes, it will be a LEFT OUTER JOIN, although i’m guessing that an article that has not been read at all will not be of interest, so you can probably get away with an INNER JOIN

3

I should join them by inner join then… and I meant 30 days backwards from today.

My current problem is how do I make a count in the join. I know how to make a join, but how do I make a :


count (all from a column that have the same id) as visits

?

4 
SELECT articles.id
     , articles.title
     , COUNT(*) AS visits
  FROM articles
INNER
  JOIN visits
    ON visits.article_id = articles.id
   AND visits.visit_date > CURRENT_DATE - INTERVAL '30' DAY
GROUP
    BY articles.id
ORDER
    BY visits DESC

:slight_smile:

5

Good response! That is what i was searching for… I thank you very much!