FAQ: Subqueries - Subqueries

This community-built FAQ covers the “Subqueries” exercise from the lesson “Subqueries”.

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

Analyze Data with SQL

FAQs on the exercise Subqueries

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

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

Ask or answer a question about this exercise by clicking reply (reply) below!
You can also find further discussion and get answers to your questions over in Language Help.

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

Need broader help or resources? Head to Language Help and Tips and Resources. If you are wanting feedback or inspiration for a project, check out Projects.

Looking for motivation to keep learning? Join our wider discussions in Community

Learn more about how to use this guide.

Found a bug? Report it online, or post in Bug Reporting

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!

In this exercise, how come we can’t use JOIN as a solution? I tried to run it but it gave me an error.

What is the join query you try to execute? Could be that you had ambiguous columns.

the exercise will not accept the JOIN solution regardless (due to the way the exercise validates)

Thank you. I was just trying to compare the solutions between JOIN and Subquery.

I tried to run this:

SELECT first_name, last_name
FROM band_students
JOIN drama_students
ON band_students.id = drama_students.id;

Its exactly what I predicted, you have an ambiguous column problem. The first name and last name field/column occur in both tables. So Sqlite can’t deal with it, here is some more information on the matter:

Sqlite : ambiguous column name - Stack Overflow

1 Like

Wow, I don’t know how I missed it. Thank you. Have a nice weekend.

Just wanted to note, I came here to check the exact same thing as user ykstate - I completed the exercise and decided to try and replicate the same result with a JOIN for practice. I already thought the ambiguous columns were the issue but it’s a little strange that the example JOIN provided in the exercise DOES show ambiguous columns being used, caused me to second guess and think they don’t need specified.

If we’re using SQLite, then the example on the left should probably match the restrictions of the database engine we’re using. Fantastic course, just been tripped up a few times with similar issues

2 Likes

Capture 12

How can it be that the tables can be combined? The id’s are the primary keys and are unique, so I am bit confused as to why they should be the same?