The company is performing an analysis of their inventory. They are considering purging books that are not popular with their customers. To do this they need a list of books that have never been purchased. Write a query using a join that provides this information. Your results should include all the book details and the order number column. Sort your results by the book title.

 SELECT o.order_nbr, b.*
 FROM orders o JOIN books 
 WHERE 
 ORDER BY book_title

This is all I could come up with, I'm still learning Joins and struggling to figure out what the correct statement should be. Wasn't sure what to put in the WHERE clause and don't really know how to properly join these tables.

CodePudding user response：

You need an ON clause to specify what you are joining on. Also, your WHERE clause is empty, and you are not specifying the type of JOIN you are using. Looking at the way the tables are set up, the expectation is you are going to join the BOOKS table on ORDER_ITEMS, which also contains ORDER_NBR.

In the question, it's asking to find books with no orders, so correct join would be a LEFT JOIN between BOOKS and ORDER_ITEMS, as that will include every book, even those without orders, which will have an ORDER_NBR of NULL

The SQL would look like

SELECT o.order_nbr, b.*
 FROM books b
 LEFT JOIN order_items o on b.book_id = o.book_id
 WHERE o.order_nbr is null
 ORDER BY book_title

This would return only the books with no orders.