troubleshooting Question

Outer join issue with too many results

Avatar of termlimit
termlimitFlag for United States of America asked on
SQL
5 Comments1 Solution223 ViewsLast Modified:
I currently have a table that has a list of books (14) and a table that has a list of books that were ordered, along with another table that has the order details.

I want to try and select the 14 books and associate the books that were ordered information with the correct book title (there are only 11 of the 14 ordered).

I have tried every join I can think of but I always wind up getting 32 or 35 results.

The latest that I have is as follows:
select book.title, bookorder.orderdate, order_items.order#
from order_items
right join book
on book.isbn = order_items.isbn
right join bookorder
on bookorder.order# = order_items.order#;

This one gives me 32 results.  There are 14 book, 32 order_items, and 21 bookorder.  So instead of matching up to the 14 it is matching up to the 32.  I have tried reversing the order of everything but it just seems to add the 21 to the 14 rather than limit it to 14.

I am out of ideas at this point.  Again I would like it to list the 14 book titles.  Then the order date if there is one and the order# if there is one.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros