Where clause with inner join not working for orders table

Question

I have the following tables(with their columns in brackets):

products(productID, productName, categoryID)
categories(categoryID, categoryName)
customers(customerID, customerName)
orders(customerID, create_at, productID)

I can successfully use inner join to display all the order details and include the product and customer names.

select products.productName, orders.create_at, customers.customerName 
from orders 
inner join products on orders.productID=products.productID 
inner join customers on orders.customerID=customers.customerID;

Now I want to display only the orders in which the product belongs to category "laptop". And my attempt was the following:

select products.productName, orders.create_at, customers.customerName 
from orders 
inner join products on orders.productID=products.productID 
inner join customers on orders.customerID=customers.customerID where categories.categoryName='laptop';

But this doesn't work and I assume it is because the where clause should be based on a column that belongs to the orders table.

The error I get is the following:

ERROR 1054 (42S22): Unknown column 'categories.categoryName' in 'where clause'

How can I show only orders which products belong to a specific category?

I didn't join categories because the categoryID is not included in the orders table. — user agent

forpas forpas · Accepted Answer · 2020-04-11T14:05:02

You must join to the table categories:

select products.productName, orders.create_at, customers.customerName 
from orders 
inner join products on orders.productID=products.productID 
inner join categories on categories.categoryID=products.categoryID
inner join customers on orders.customerID=customers.customerID 
where categories.categoryName='laptop';

There must be a column like categoryID in both products and categories.
Sometimes a product may belong to more than 1 categories. In this case there must be another table that links products to categories.

Where clause with inner join not working for orders table

1 Answers