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?
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.