Hey everyone - having trouble completing this exercise.
Everything works fine until Step 12, after then nothing will filter or order how it’s supposed to. I get results for all of the dishes that exist, not the specific ones (nor the spicy ones in the step after).
Consulting the hint:
You will be making this query from three tables: menu , dish and category . Use the ORDER BY keyword to sort the results by a specific column.
I’ve tried using order by, and it doesn’t reduce the results. In addition, we don’t actually have a “menu” table, so I’m a bit worried I might have missed a step or something is wrong internally?
Try using the tables: dish, categories_dishes, and category, all of which were created and had data inserted in the previous steps you completed. Those were the names of the tables based on the instructions in Steps 3, 4, and 8, so not sure why Codecademy called them something else in the hint there.
Maybe the hint above with the correct table names and references to where they were created will help so you can examine the data and the relationship between all the tables.
select dish.name as dish_name, category.name as category, categories_dishes.price as price from dish, categories_dishes, category order by dish.name desc;
i presume this is right - based on what is asked for. i get 8 different dishes, but they’re in different price / category sections. order by doesn’t filter, neither does sort by. should i be averaging the price or something? i’m just a bit confused what i’ve done wrong as per the instructions - clearly what i’ve done is wrong but idk where i should be going?
When I tried the SQL query you wrote, I get a lot more results than just 8. The reason is because the relationships between the tables aren’t being used to limit the results. There are different ways to join the tables together, including different JOINs, but you can also use WHERE. Some of the previous steps of the project included the creation of primary keys and foreign keys so that the database knows how the fields in each table are related.
Check what you did for Step 10 of this project for an example. In the last part of that step, you had to write a query that displayed the restaurant name and the address. To do that, you had to use the restaurant and address tables. Most likely, your SQL query included a WHERE clause that looked something like WHERE restaurant.id = address.restaurant_id
You can do something like that with a WHERE clause in the query you posted. Review each table to see which fields relate to fields in another table (in other words, look for the primary and foreign keys).
Hey Scott! I’m struggling with this query. I cannot seem to understand how am I supposed to use the WHERE clause.
On the other hand, I have this question… how are we supposed to have 8 results in the query if the dishes may belong to different categories? I mean… if we are supposed to show dish name, price and category… which combination of price-category should we pursuit?
You don’t have to use a WHERE clause if you’d like to use an INNER JOIN. Either one would be effective here. By making sure that the relationships of the table are represented in the SQL query, then you’ll be limiting the records to only those that have the right connections with each other.
I’m not sure which path you’re on exactly and the lesson I’m about to reference is included in multiple ones, but here’s the link to it if you’re on the Full-Stack Engineer Path: Designing A Database - One To Many Exercise. In case you’re on another path, look for the “What Are Database Relationships?” prior to the Build a Menu for Bytes of China project.
Now for the reason I linked that particular lesson. In the instructions of this one, you had to create an INNER JOIN for Step 5. The query probably looked something like this:
SELECT book.title AS book_title, chapter.title AS chapter_title,
page.content AS page_content
FROM book
INNER JOIN chapter
ON book.isbn = chapter.book_isbn
INNER JOIN page
ON chapter.id = page.chapter_id;
The same results could be achieved with this:
SELECT book.title AS book_title, chapter.title AS chapter_title,
page.content AS page_content
FROM book, chapter, page
WHERE book.isbn = chapter.book_isbn AND
chapter.id = page.chapter_id;
NOTE:The above examples are only provided because to get to the current project, you would have already had to complete that lesson.
Even though it’s possible that the dishes may belong to multiple categories and a category can contain multiple dishes (many-to-many), not all the dishes end up having a category so they don’t show up in the output once you write the query. For example, ‘Sweet and Sour Chicken’ doesn’t have a category assigned to it in categories_dishes. 8 just happens to be the number of rows you’ll have if you write the query with the correct relationships in the WHERE clause or INNER JOIN.
For Step 12, you should pursue every valid combination between dish, category, and categories_dishes.
As Step 8 points out, there is a many-to-many relationship between dish and category, so categories_dishes was created to be the cross-reference table.
When you’re writing your query for Step 12, keep in mind how each of the tables are connected.
I hope this helps to give you a nudge in the right direction
i found this really confusing mostly because of the wording of the question. this should definitely be looked at by the Codecademy team, maybe @alyssavigil can flag it.
the hint is also wrong, and the combination of both led me away from the solution.
as @selectall states, the trick here is the relationship between dish / category / categories_dishes - how you would link a dish to a category through dishes_categories - or how do you know a dish corresponds to a category using the dishes_categories table?
Hello everyone,
I am posting my solution here both as a reference for other learners to look at and also to get feedback on how to improve my script and make it easier to read.
Readability is one of my main problems with SQL.
I agree with @matthewwillis4598398 on how some of the questions and hints can be confusing.
There is also a mistake in the way the date is formatted in the projectdata.sql file.
It is my impression also that some key concepts are missing in the lessons.
Just one last thing: as an Ubuntu user I could not follow the instruction to use Postbird as client.
I had to use, and l am still using, the command line.
Hello @maingocanh1702,
thanks for your message.
I have just imported the script in Postgres and it ran without any error.
From what I see in your screenshot there is a typo in your query (categpry.name while it should be category.name).
Please let me know if it works once the type is fixed or if you find other problems.
It’s always good to use an extra pair of eyes
Good Luck coding and have fun
So the hint is… not really a hint. It’s totally misleading. It suggests you’re supposed to have a ‘menu’ table, which you aren’t.
It also doesn’t point you towards what you actually need to do, which is to do an INNER JOIN between your category_dishes table, and both the tables that make it, ON the correct keys.
Once you do this, with an AS statement in the select to get that heading right, it gives you the correct eight rows of results.
