SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Left outer join and count

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :

    Code SQL:
    COUNT (ALL FROM a COLUMN that have the same id) AS visits

    ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good response! That is what i was searching for... I thank you very much!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •