# SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

## Many to Many relationships

If anyone has done some shopping on the internet you are familiar with the term “Shopping Cart” or “Shopping basket”. After you have selected a product you want to buy the storefront will gladly let you keep on shopping until there are many items in you shopping cart. On my last trip to Amazon.com I put 3 things in my cart before checking out. One item was a HDMI cable box converter for my flat screen TV at home. I took a closer look at this products rating and reviews before buying. Below this product it said this items was in the top 100 selling items in home electronics. This means this items has sold to more people than just me and I was not the first to buy this product.

Based on the story above what is the relationship to a product (like the HDMI cable box) and a customer who may want to buy that item? As a customer I can buy many products at once in my shopping cart. Does this mean there is a one to many relationship between customers (like me) and products (like the HDMI cable box). Yes there is but that is only a half truth. A product like the HDMI cable box can be purchased by many different customers. Therefore there is a Many to Many relationship between Customers and products. This product can be bought by many customers and many customer can buy this product.

Another many to many relationship is between books and Authors. I have written 5 books so we know there is at least a one to many relationship between Authors and Books. Now look at my 4th book (SQL Programming Joes 2 Pros Volume 4 – ISBN-13: 978-1451579482). This book has two authors (Rick A. Morelan and Pinal Dave). Since an Author can write many books and a book can be written by many authors there is a many to many relationship between authors and books.

We need a new process to map this complex relationship. Sometimes tables exist for the sole purpose of allowing indirect relationships between tables. What do you call tables the handle relationships for other tables? Common terms for these type of tables are mapping tables, bridge tables, or junction tables.

## Many to Many relationships with invoicing systems

As discussed earlier a product can appear on many invoices and an invoice can have many products. The relationship between products and invoices is known as a many-to-many relationship as seen in the figure below.

If you were to ask yourself “What Products are on Invoice 5631?” or “Go-Duck was ordered on how many invoices?” you could resolve this query? The Figure below shows us how to resolve both mappings. We can see the Go-Duck toy was order twice (on 7/15 and on 9/22). We can also see the shopping cart 5631 has two items in it (Toy Car and Furchee).

Now we’ll take a look at many-to-many relationships between sales invoices and products in the JProCo sample database.

Here is an example of JProCo’s sales invoices mapping to a bridge table (SalesInvoiceDetail) in order to map over to the CurrentProducts table. The CurrentProducts table gives us all the detail of the current products that have been ordered.

With SalesInvoice 5 It looks like many products were ordered on that one invoice (Products 9, 11, 12, and 16). To see what those products are, we would look over to the CurrentProducts table. We see Product 9 is an Underwater Tour 3 Days East Coast. Product 11 is an Underwater Tour 1 Week East Coast, and so forth. So a SalesInvoice can have many products, and products can be ordered on multiple sales invoices.

The SalesInvoiceDetail table is the mapping table that is handling the many to many relationship between the SalesInvoice table and the CurrentProducts table. This 3 table join between these tables would look like the query you see below.

`SELECT si.CustomerID, si.InvoiceID, si.OrderDate, sd.Quantity, cp.ProductName, cp.RetailPrice FROM dbo.SalesInvoiceDetail AS sd INNER JOIN dbo.SalesInvoice AS si ON sd.InvoiceID = si.InvoiceID INNER JOIN CurrentProducts AS cp ON cp.ProductID = sd.ProductID `

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter3.0Setup.sql script from Volume 2.

## Question 8

Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490
2. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490
3. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
WHERE EXISTS (dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490)

### Rules:

Every day one winner will be announced from United States.
Every day one winner will be announced from India.
Winner from United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

## SQL SERVER – Difference Between UPDATE and UPDATE()

• DiveSh Singhvi
August 9, 2011 3:12 am

1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Divesh
INDIA

• Correct Answer is Option : 1.

SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Bablu

Country : – INDIA

• Answer 1 is the correct one

SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

• correct answer, is option 1 modified,
SELECT si.CustomerID,sd.UnitDiscount,cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490
C. Lennick, Country: USA

• Correct answer is option 1:

1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Shilpa

India

• Option 1

1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Thanks

Shree

August 9, 2011 5:02 pm

Question 8

1- SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Chennai ,INDIA

• srinivas Reddy
August 9, 2011 7:57 pm

Country USA

• Alok Chandra Shahi
August 9, 2011 10:08 pm

Option 1 is the right Answer,I.e.

SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Because Here we want Discount from SalesInvoiceDetail Table and For Customer No 490. CustomerNo exist in SalesInvoice Table and Discount is available in SalesInvoiceDetail Table and their must be a relation between SalesInvoice and SalesInvoiceDetail Table to get the exact value and that we are performing in above given query.

Alok Chandra Shahi
India

• Venkataraman R (@venkataramanr)
August 10, 2011 3:21 am

Option 1 is correct. Because, it considers both SalesInvoice table holding CustomerID. Other tables dont have CustomerID column. Option 2 and Option 3 queries will fail, as they dont have the Select clauses aliases in the FROM Clause. So, the correct answer is Option 1, which will successfully get the results.

1) 1.SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Venkataraman R,
USA.

• La respuesta la opcion 1 es la correcta
thanks!

• Q 8) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

A.) The code in (2) does not look at the SalesInvoice Table which contains the CustomerID field so it will return an error. The use of EXISTS with a subquery in the WHERE clause will either return every row in the SalesInvoiceDetail table or none of them, regardless of the CustomerID, so (3) is also incorrect. Because ‘dbo. SalesInvoiceDetail sd INNER JOIN dbo.SalesInvoice si ON sd.InvoiceID = si.InvoiceID’ will return only records that have a match from both tables and ‘WHERE si.CustomerID = 490’ will filter the results to only display records having a CustomerID of 490, (a) is the ALMOST correct answer.
However, the SELECT of the query references sd.CustomerID, which is wrong as CustomerID belongs to SalesInvoice table.

```The correct answer is
SELECT si.CustomerID, sd.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp — if we need to show Product Names we join with CurrentProducts table
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490
```

This one was indeed Tricky one to test how many really reads the whole blog post :)

Winner from USA: David Seefeld

Winner from India: sandipak

I thank you all for participating here. The permanent record of this update is posted on facebook page.

• Both the winners are from USA

• Both Winners are from USA…..

• Option 1 is the right Answer,I.e.

SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Because Here we want Discount from SalesInvoiceDetail Table and For Customer No 490. CustomerNo exist in SalesInvoice Table and Discount is available in SalesInvoiceDetail Table and their must be a relation between SalesInvoice and SalesInvoiceDetail Table to get the exact value and that we are performing in above given query.

India

• Uday Bhoopalam
August 16, 2011 9:33 pm

Option 1 is the correct query

Uday
USA

SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
WHERE si.CustomerID= 490

Thanks,
Wayne

• Option 1 Is the only query that would compile, but the correct answer is none of the above. Either:
SELECT DiscountAmt
FROM dbo.SalesInvoice
WHERE CustomerID= 490
OR
SELECT SUM(DiscountAmt) [Total Discount]
FROM dbo.SalesInvoice
WHERE CustomerID= 490
Depending on whether the question was a list of the individual discounts the customer got with each order or if they wanted the total discount. The problem with the first one is that it gives detailed information with no context. (When did the customer make each order, etc.) The second might benifit from more context as well:
SELECT SUM(DiscountAmt) [Total Discount], COUNT(InvoiceID) [Total Orders]…

Interesting, I made the assumption that DiscountAmt was an aggregate amount stored in the invoice table. It isn’t in your table diagram. (Made sense, discount amount could be affected quantity ordered, etc. and I don’t mind some denormalization…)
Making some more assumptions about what is wanted and using just diagrammed table schemas, I come up with:
SELECT cp.ProductName, COUNT(DISTINCT si.InvoiceID) [Total Orders]
, SUM(sd.Quantity*sd.UnitDiscount) [Total Discount]
FROM dbo.SalesInvoiceDetail sd
INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID AND si.CustomerID=490
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID
GROUP BY cp.ProductName

If you want, throw in “si.CustomerID, ” (NOT sd. – I take it back, NO option given would compile with the given schema) to both the SELECT and GROUP, but I’d only do it if more than one customer would be listed. (Would be an interesting schema if the customer was in sd, because that means a single invoice could be set up for multiple customers.) I put in DISTINCT because a unique index on “invoice, product” ids wasn’t declared in the detail table. (Logical schema setup and then distinct is only needed if you want a one line result for all products)
Sorry, I wasn’t consise, didn’t read all the way down to see if these errors were spotted or not.