Answer by Chris Petersn for SQL Database - Taking one columns max value for another table column

I'd add a PapersAuthors table to facilitate Authors having multiple Papers. I'd add a TopicsPapers table so a topic can have multiple papers and a paper can be in multiple topics. If a paper shouldn't be in multiple topics then don't have TopicsPapers and put a topic_id in Papers.

I'm not sure I understand what you are trying to achieve with SOTAResult but I believe you want to find the "best paper" for a topic. In that case move SOTAResult to Papers and find it using a "limit 1 subquery":

SELECT t.*, (SELECT paper_id FROM Papers AS tp WHERE tp.topic_id=t.topic_id ORDER BY SOTAResult DESC LIMIT 1) AS top_paper FROM Topics AS t;

Friday 26th April 2019 9:49 am

Back to User Chris Petersn - Stack Overflow blog