Multiple Tables With Reddit

Hi everyone,
I’m going through the project “Multiple Tables with reddit”, and step number 6 is confusing with what they’re asking for.

" Over time, posts may be removed and users might delete their accounts.
We only want to see existing posts where the users are still active, so use an INNER JOIN to write a query to get these posts. Have the posts table as the left table."

I believe this to be the code they’re looking for

SELECT * FROM posts 
JOIN users ON
posts.user_id = users.id;

My issue with this is that the tables are being joined on a userid and not a username. If you take a look at the results, they’re are multiple nulls with usernames. Does the null here indicate that the account was deleted? And we would need to add a is not null statement like this

WHERE users.username IS NOT NULL;

Either way, the wording of this part of the question threw me off as the data didn’t seem to line up with what was being asked.

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

2 Likes

Yep, that’s correct.

username doesn’t exist in the posts table. However, user_id does, which also exists as the PRIMARY KEY (as id) in the users table.

I think there are NULLS w/username b/c the person didn’t create a username(?)

1 Like

Yes I think the null in username signify that the account might be deleted that’s why we have chosen id which is primary key and hence this can not be null