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?
- 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 - 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 - 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)
Please post your answer in comment section to win Joes 2 Pros books.
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
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)
123 Comments. Leave new
Correct option 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
as SalesInvoiceDetail makes an inner join with SalesInvoice on InvoiceID and further for every invoice we have productid, so SalesInvoiceDetail makes an inner join with CurrentProducts for CustomerID=490
Sumit
India
Correct Answer : 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
Shekhar Gurav.
Country – INDIA
The correct answer is #1
USA
Mike Michalicek
Correct 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
By this query, system will identify invoices For customer: “WHERE si.CustomerID= 490” and then Find out Invoice details by “dbo.SalesInvoiceDetail sd INNER JOIN dbo.SalesInvoice si
ON sd.InvoiceID= si.InvoiceID”
Country: INDIA
Tejas
Correct option is number 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
INDIA
Answer: 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
Country: India(sikha)
None of them acutally run on the sample database. I assume there is just a typo in #1 and that the answer should be (note I changed the alias/column names to match your screen shot to get this to work for CustomerID and sd.DiscountAmt)
1)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
I’m picking 1 assuming typos because #2 references columns that do not exist in either table and #3 exists clause is incorrectly written
from the USA
Option 1 is the Correct answer
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
It has relation between SalesInvoice and SalesInvoiceDetail and also between CurrentProducts and SalesInvoiceDetail tables
Yasodha.N(India)
Hi Pinal,
I think given queries are wrong because SalesInvoiceDetail Table Not Having CustomerID column. please check it.
Hi Pinal,
I think given queries are wrong because SalesInvoiceDetail Table not having CustomerID column. please check it.
Otherwise if alias interchanged then option 1 is correct.
Pune,India.
Answer 1 is correct
INDIA
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
Texas, USA
Here is the answer:
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
Texas, USA
None of the answers are correct, however the “most” correct option is 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
Explanation: To filter on customerID we need to join on salesinvoice si. The question tells us to get the discount amounts from the salesinvoicedetail sd, but the discount amount being retreived in option 1’s select statement comes from salesinvoice si. The question also tells us that the customerID field is in the salesinvoice table si. We’re filtering on the correct customerID field, but I think the table aliases were transposed in the SELECT portion of the statement and shoudl be si.CustomerID and sd.DiscountAmt.
These could just be a typos. Other than that, the joins are correct so that we could get the information we need.
Option2 doesn’t join against salesinvoice which contains the customer ID
Option3 is not a valid statement
Country of Residence: USA
Option 1 is correct answer.
Bangalore, India
Option 1 is Right
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
Yeou Sunn
India
Option 1 is correct in logic but need to fix typo: CustomerID is in si and DiscountAmt is in sd:
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
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
Chetan – 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
kkmjssate
india
Hi Pinal,
Challenge:
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?
Correct Answer:
The first choice is the correct answer:
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
Explanation:
The first query is correct. The second query doesn’t even reference the SalesInvoice table, so that query is discarded. The third query uses a WHERE EXISTS incorrectly and won’t compile.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping