Q 8 - Multiple Tables with Reddit

Multiple Tables with Reddit (https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-multiple-tables-with-reddit)

Hello everyone,

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;

Thank you for your help in advance!!

1 Like

Hello! When you INNER JOIN on a table, you use the popular_posts table. So, what should you be using here:

Should it be posts or popular_posts?

4 Likes

Hi, I did the same, but in this section, I put this:

SELECT name, title, score
FROM subreddits
INNER JOIN popular_posts
ON subreddits.id = popular_posts.subreddit_id
ORDER BY popular_posts.score desc;

I’m Not sure if it’s ok, hope it helps someone!

Why is this code not working for this example?

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

order by popular_post.score desc;

Double check your table/column names, any mistakes could prevent it running.

Hi intpw,

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.

Hope this helps!

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

Thanks for letting me know! Cheers

1 Like

I ended with this

"WITH popular_posts AS (SELECT *

FROM posts

WHERE score >= ‘5000’)

SELECT subreddits.id, popular_posts.score, popular_posts.id

FROM subreddits

INNER JOIN popular_posts

ON subreddits.id = popular_posts.id

ORDER BY popular_posts.score DESC

;"

2 Likes

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;