WITH popular_posts AS (
SELECT * FROM posts
UNION
SELECT * FROM posts2
WHERE score >= 5000
AND WHERE score IS NOT NULL
)
SELECT subreddits.name, popular_posts.title, popular_posts.score
FROM subreddits
JOIN popular_posts
ON subreddits.id = popular_posts.subreddit_id
ORDER BY popular_posts.score DESC;
The first part works well when it is not in the WITH clause, but the complete code does not return any result.
Try breaking it up to see what section does work, e.g. get everything then start adding additional clauses as needed. When you add something and get no output then you know that’s the bit which causes the problem (stuff like editing out lines with -- might make this easier).
I will note that the WHERE clause is not repeated for additional conditions (chain them with logic if needed).
Thank you @tgrtim.
The mistake was in the WHERE clause as you noted it.
The good thing is that I retry today before I read your answer and I found the solution by myself
hi,
could you please share your solution? i also wrote the same code as your original one, and i still cannot find the correct answer joining posts and posts2.
thank you