What should I used as my foregin key in SQL

Hi, Please could someone offer some advice for the following query.

  • I’m not sure what the foreign key should be, id or score?

Now you need to find out which subreddits have the most popular posts. We’ll say that a post is popular if it has a score of at least 5000. We’ll do this using a WITH and a JOIN .

First, you’ll need to create the temporary table that we’ll nest in the WITH clause by writing a query to select all the posts that have a score of at least 5000.

Next, place the previous query within a WITH clause, and alias this table as popular_posts .

Finally, utilize an INNER JOIN to join this table with the subreddits table, with subreddits as the left table. Select the subreddit name , the title and score of each post, and order the results by each popular post’s score in descending order.

WITH popular_posts AS (

SELECT *

FROM posts

WHERE score >= 5000

)

SELECT subreddits.name, popular_posts.title, popular_posts.score

FROM subreddits

INNER JOIN popular_posts

ON subreddits.id = popular_posts.id

ORDER BY popular_posts.score DESC;

The column from the original table from which you use as a foreign key should be unique, non-null and all that good stuff (even if it is not in the table of interest). So consider which columns fit the bill for this.

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.