How to work out this one-to-many join?

Vernon · #1 (**permalink**) 09-21-05, 14:29

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

r937 · #2 (**permalink**) 09-22-05, 07:18

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

Vernon · #3 (**permalink**) 09-22-05, 18:48

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.

r937 · #4 (**permalink**) 09-22-05, 22:56

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

Internet Services

Web Development

Digital Marketing

Consumer Tech