I’m stuck with question 8…Could anyone have any advice for this?
This is what I have now.
WITH popular_posts AS (
SELECT *
FROM posts
WHERE score >= 5000
UNION
SELECT *
FROM posts2
WHERE score >= 5000
)
SELECT subreddits.name, popular_posts.title, popular_posts.score
FROM subreddits
INNER JOIN popular_posts
ON subreddits.id = posts.subreddit_id
ORDER BY popular_posts.score DESC;
There is a minor issue, the title and score need to come from popular_posts. Title and score are not in the subreddits table so you will not get a result for your query.
It should be alright, the select statement is operating on the combined table, not just subreddits. Personally I quite like explicitly stating which table the column should come from but it’s not essential (unless you have repeated name issues).
Hello, Can anyone please help me with this query… somehow it’s not giving me any result. Please advise!
WITH ‘popular_posts’ AS(
SELECT *
FROM posts
WHERE score >= ‘5000’
UNION
SELECT *
FROM posts2
WHERE score >= ‘5000’)
SELECT subreddits.name,
popular_posts.title,
popular_posts.score
FROM subreddits
INNER JOIN popular_posts
ON popular_posts.id = subreddit.id
ORDER BY popular_posts.score DESC;
This was the solution for me. Maybe you can find the error by comparing. Errors I’ve seen so far →
Using ‘quotes’ when naming ‘‘with’’.
Using quotes for the number 5000
when ending with ; to use
WITH popular_posts AS (
SELECT *
FROM posts
WHERE score > 5000
UNION
SELECT *
FROM posts2
WHERE score > 5000
)
SELECT
subreddits.name,
popular_posts.title,
popular_posts.score
FROM subreddits
INNER JOIN popular_posts
ON subreddits.id = popular_posts.subreddit_id
ORDER BY popular_posts.score DESC;