FAQ: Code Challenge: Multiple Tables - Code Challenge 3

[wiki-bot]

This community-built FAQ covers the “Code Challenge 3” exercise from the lesson “Code Challenge: Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development

FAQs on the exercise Code Challenge 3

• Why isn’t there a field for whether a user is premium in the users table?

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply () below!

Agree with a comment or answer? Like () to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

[cyraneika]

Since all the users in the premium users table are premium users, shouldn’t there be a special “join” we can use to only show these? so we wouldn’t need the “where” condition? like an inner join for example?
I just tried it and it worked, as in has the same count of user ids as the initial answer: 100.
this is my code:
SELECT COUNT(id)
from users
INNER JOIN premium_users
ON users.id = premium_users.user_id;

haha, nevermind, I thought we only wanted premium users but instead we want the free users
but if we only wanted premium users we could do an inner join…

[vahe_h]

Hi all
How does the

SELECT users.id,
users.first_name
FROM users
LEFT JOIN premium_users
ON users.id = premium_users.user_id
WHERE premium_users.user_id IS NULL

finds NULL users when:

SELECT *
FROM users
;

SELECT *
FROM premium_users
;

does not show any NULL rows in user_id from the table/data.
?!

[mythling]

Kind of feel this code challenge is too hand-holding, would be more challenging (and would cement previously taught topics) if there was less guides on how to bring up prompted question. What I mean by hand holding:

" Use a LEFT JOIN to combine users and premium_users and select id from users .

The column id in users should match the column user_id in premium_users .

Use a WHERE clause to limit the results to users where premium_users.user_id IS NULL . This will remove premium users and leave you with only free users."

Less of this would be better in my opinion.

[roaguirred]

There’s too much information in the instructions, It’s just too easy to just write what the instructions say.

“Which users aren’t premium users?” and "select id from users" is enough information. The rest could be provided as a hint.

[ernestohinojosa]

I would also love a clarification on how the solution code matches IS NULL.

Solution Code:

SELECT users.id
FROM users
LEFT JOIN premium_users
	ON users.id = premium_users.user_id
WHERE premium_users.user_id IS NULL;

Thanks in advance!

P.S. Just like other learners mention in this particular thread, there is to much information in the instructions. A more realistic scenario would be that we get a visual representation of what is expected, then we should try to produce it.

[ervin.wav]

I believe after left join is performed it will create empty cells in the query result (premium_users.user_id) .
You could use this code to visualize this.

SELECT *
FROM users
LEFT JOIN premium_users
ON users.id = premium_users.user_id

LIMIT 10;

The main problem i find with the way join concepts are taught here is lack of explanation of exact logic behind different kind of joints. I always have to google for more visual examples to grasp these concepts.

[sionchen]

Agreed, the first line should have sufficed. The rest should have been hints.

[zsuzsa_lukacs]

So, what I do is only read the first line, try to implement it, and then it is not accepted I read the rest and adjust accordingly.

This is my solution for this one. I think its more clear:

  SELECT id, membership_plan_id
  FROM users
  LEFT JOIN premium_users
  ON users.id=premium_users.user_id
  WHERE membership_plan_id IS NULL;

[micro4837016354]

(Um, actually) wouldn’t this be technically a more correct solution than limiting to results WHERE premium_users.user_id IS NULL? :

SELECT * FROM users LEFT JOIN premium_users ON users.id = premium_users.user_id WHERE purchase_date IS NULL OR cancel_date IS NOT NULL;

Or is the idea that at that point they are non-users rather than free users?

[byte5846683115]

Hi together,
I would like to understand why this doesn’t yield any results, whereas a pure count (not a distinct out) actually works out and gives 100 as a result?

Thanks in advance

[ykstate]

Do you know why we’re matching these two columns?

ON users.id = premium_users.user_id

shouldn’t be this

ON users.id = premium_users. membership_plan_id?

membership_plan_id is the foreign key.

[core1823502904]

VALID FAQ covers the “Code Challenge 3” exercise from the lesson “Code Challenge