Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Red face Unanswered: How to work out this one-to-many join?

    table Article ( id int primary key, ...);
    table View ( id int primary key, articleID int references Article(id) on delete cascade, ...);
    table Topic ( id int primary key, topic varchar(20) not null, articleID int references Article(id) on delete cascade);

    I need to have the most viewed articles and the most viewed article on a given topic. I thought I could have a group by on the articleID on the View table. Can I use "order by count(*)" to finish the query statement?

    Can someone point me the right direction to work it out?

    Any help will be highly appreciated.

    Vernon

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    most viewed articles --
    Code:
    select Article.id
         , count(View.id) as views
      from Article  
    left outer
      join View
        on Article.id = View.articleID  
    order
        by views desc
    but as far as "most viewed articles on a given topic" is concerned, you need to re-examine the table design, apparently each of your topics can have only one article in it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Posts
    24
    Thanks very much Rudy. You're always very helpful.

    I will study your query. While studying and re-examine my design of those domain object/DB tables this morning, I recognize a few errors. The Topic table is one of them. I should need two tables instead:

    table topic (id int primary key, name varchar(9) not null);
    table article_topic(article_id int references Article(id) on delete cascade, topic_id int references Topic(id) on delete cascade);

    Since I am in Hibernate, I need to redesign those domina objects and tables to take the advantage of HQL.

    Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vernon
    I should need two tables instead:

    table topic (id int primary key, name varchar(9) not null);
    table article_topic(article_id int references Article(id) on delete cascade, topic_id int references Topic(id) on delete cascade);
    yes, that's good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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