Olek Nowakowski Olek Nowakowski - 4 months ago 7
PHP Question

Querying multiple tables on MySQL

I have been studying MySQL through a book called 'PHP and MySQL Web Development' and I'm on a chapter that teaches how to query data from multiple tables, I was trying to do it on my own and came up with the following command

mysql> select customers.name from books, customers, orders, orders_items
-> where books.title = 'Java 2'
-> and books.isbn = orders_items.isbn
-> and orders_items.orderid = orders.orderid
-> and orders.customersid = customers.customerid;


and it returns me the following error

ERROR 1146 (42S02): Table 'books.orders_items' doesn't exist


but when I try to use the command exactly as it is in the book it works just fine

mysql>select customers.name from customers, orders, order_items, books
->where customers.customerid = orders.customerid
->and orders.orderid = order_items.orderid
->and order_items.isbn = books.isbn
->and books.title = 'Java 2';

+-------------+
| name |
+-------------+
| Julie Smith |
+-------------+


What am I missing here besides the order of the criteria?
I have not typed books.orders_items anywhere.

Answer

You're using the plural orders_items not the singular from the book order_items

both here

select customers.name from books, customers, orders, orders_items

and in line

and books.isbn = orders_items.isbn