CODEWITHSUNDEEP
sqljoin
user2059704
user2059704

Reputation:

Joining 5 tables in SQL

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

Upvotes: 3

Views: 48899

Answers (4)

czy
czy

Reputation: 51

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

Upvotes: 5

michelle.ann.diaz
michelle.ann.diaz

Reputation: 67

Try to use GROUP BY statement

Like this:

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 BookTitle

Upvotes: 0

Terje D.
Terje D.

Reputation: 6315

As there is no relation between the books and the records belonging to one order, you should not join them together, but instead use two distinct queries to find the information on books and records.

One possibility is then:

SELECT 'Book' AS Type
, Books.Title AS Title
, Books.Cost AS Cost
, OrderedBooks.Quantity AS Quantity
, OrderedBooks.Total AS Total
FROM Orders 
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID 
INNER JOIN Books ON OrderedBooks.BookID = Books.ID 
WHERE (Orders.ID = 9)

UNION

SELECT 'Record' AS Type
, Records.Title AS Title
, Records.Cost AS Cost
, OrderedRecords.Quantity AS Quantity
, OrderedRecords.Total AS Total
FROM Orders 
INNER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID 
INNER JOIN Records ON OrderedRecords.RecordID = Records.ID 
WHERE (Orders.ID = 9)

Upvotes: 0

Paul Grimshaw
Paul Grimshaw

Reputation: 21014

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)

Upvotes: 1

Related Questions