[sql] Joining Multiple Tables - Oracle

I'm studying multiple table joins this week and have some odd results being returned. Here is the scenario...

Using the correct tables, create a query using the traditional join operation that will list the customer first and last name, book title, and order date (formatted as MM/DD/YYYY with an alias of “Order Date”) for all the customers who have purchased books published by 'PRINTING IS US'.

With the database that I'm querying against, the correct tables for this query are BOOK_CUSTOMER, BOOKS, BOOK_ORDER, and PUBLISHER. The statement that I have written returns the information that I need, but it is returning almost 5900 records. I don't see how this can be right. The publisher, Printing is Us, only has 14 books listed in the database and there are only 20 customer records, so even if every customer purchased every Printing is Us book, that would only return 280 records total. Yet I can't figure out what I have wrong. My statement is listed below.

SELECT bc.firstname, bc.lastname, b.title, TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date", p.publishername
FROM book_customer bc, books b, book_order bo, publisher p
WHERE(publishername = 'PRINTING IS US');

Anyone have any thoughts on what I'm missing here??

Thanks.

You are doing a cartesian join. This means that if you wouldn't have even have the single where clause, the number of results you get would be book_customer size times books size times book_order size times publisher size.

In order words, the result set gets blown up because you didn't add meaningful join clauses. Your correct query should look something like this:

SELECT bc.firstname, bc.lastname, b.title, TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date", p.publishername
FROM book_customer bc, books b, book_order bo, publisher p
WHERE bc.book_id = b.book_id
AND bo.book_id = b.book_id
(etc.)
AND publishername = 'PRINTING IS US';

Note: usually it is adviced to not use the implicit joins like in this query, but use the INNER JOIN syntax. I am assuming however, that this syntax is used in your study material so I've left it in.

I recommend that you get in the habit, right now, of using ANSI-style joins, meaning you should use the INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN elements in your SQL statements rather than using the "old-style" joins where all the tables are named together in the FROM clause and all the join conditions are put in the the WHERE clause. ANSI-style joins are easier to understand and less likely to be miswritten and/or misinterpreted than "old-style" joins.

I'd rewrite your query as:

SELECT bc.firstname,
       bc.lastname,
       b.title,
       TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date",
       p.publishername
FROM BOOK_CUSTOMER bc
INNER JOIN books b
  ON b.BOOK_ID = bc.BOOK_ID
INNER JOIN  book_order bo
  ON bo.BOOK_ID = b.BOOK_ID
INNER JOIN publisher p
  ON p.PUBLISHER_ID = b.PUBLISHER_ID
WHERE p.publishername = 'PRINTING IS US';

Share and enjoy.

While former answer is absolutely correct, I prefer using the JOIN ON syntax to be sure that I know how do I join and on what fields. It would look something like this:

SELECT bc.firstname, bc.lastname, b.title, TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order         Date", p.publishername
FROM books b
JOIN book_customer bc ON bc.costumer_id = b.book_id
LEFT JOIN book_order bo ON bo.book_id = b.book_id
(etc.)
WHERE b.publishername = 'PRINTING IS US';

This syntax seperates completely the WHERE clause from the JOIN clause, making the statement more readable and easier for you to debug.