Joining 5 tables in SQL

Question

I'm having trouble with an SQL statement.

Basically I have 5 tables, these are:

Books, Records, OrderedBooks, OrderedRecords and Orders.

My orders contain all the orders customers place, My orderedRecords, and orderedBooks contain all the books and records which are related to a customers order and obviously the books and records tables contain the information relating to each book and record.

My orderedrecords and orderedbooks contain a BookID and RecordID which relate to the corresponding books / records.

I'm writing an app which needs to show booktitle, bookcost, bookQuantity, booksTotal(price*quantity) recordtitle, recordcost, recordQuantity, recordsTotal.

The total for books and records does not have to be calculated using mathematical functions in the SQL.

So far I've managed to get it to give me exactly what I want, however, say I have 3 book orders, and 1 record order, for the remaining two rows of the record order, it will duplicate the data instead of simply being empty. I've tried Left and Right joins which have not worked.

Here is my SQL statement...

SELECT Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal 
-- Nothing wrong with select part
FROM Orders 
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID 
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID 
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID 
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID 
WHERE (Orders.ID = 9)

It returns the correct ordered books and records for the correct order number, however I cannot seem to get rid of the duplicated rows

Answer 1

Books.id:

SELECT Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal
FROM Orders 
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID 
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID 
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID 
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID 
WHERE (Orders.ID = 9)
GROUP BY Books.ID

Answer 2

How about a simple SELECT DISTINCT:

SELECT DISTINCT
Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal 
-- Nothing wrong with select part
FROM Orders 
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID 
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID 
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID 
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID 
WHERE (Orders.ID = 9)