When working with databases, it is common to come across scenarios where there is a need to establish relationships between tables. One such relationship is the many-to-many relationship, which can be quite complex to handle. In this article, we will explore the concept of many-to-many relationships and how they can be managed in SQL Server.
Imagine you are shopping online and you add multiple items to your shopping cart. Each item can be purchased by multiple customers, and each customer can buy multiple items. This is an example of a many-to-many relationship between customers and products. Similarly, in the world of books, an author can write multiple books, and a book can have multiple authors. This is another example of a many-to-many relationship between authors and books.
In order to handle these complex relationships, we need a mechanism to map the connections between tables. This is where mapping tables, bridge tables, or junction tables come into play. These tables exist solely for the purpose of managing relationships between other tables.
Let’s consider an example of a sales invoicing system. In this system, a product can appear on multiple invoices, and an invoice can have multiple products. This is a classic many-to-many relationship. To resolve queries like “What products are on Invoice 5631?” or “How many invoices have included the product ‘Go-Duck’?”, we can use a mapping table to establish the connections.
In the JProCo sample database, the SalesInvoiceDetail table serves as the mapping table between the SalesInvoice table and the CurrentProducts table. This table allows us to retrieve all the details of the products that have been ordered on a specific invoice. By joining these three tables, we can obtain the desired information.
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
In the above example, we are retrieving the customer ID, invoice ID, order date, quantity, product name, and retail price for each product on the invoice. This query demonstrates how the mapping table helps us navigate the many-to-many relationship between sales invoices and products.
Now, let’s test your SQL skills with a question:
Question:
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 leave your answer in the comment section below along with an explanation and your country of residence. Every day, one winner from the United States and one winner from India will be announced. The winners will receive a copy of the book “Joes 2 Pros Volume 2”. The contest is open until the next blog post is published.
Stay tuned for more SQL Server tips and tricks!