Identifying Suppliers that Only Offer Trucks and Computers

As a technical blogger, I’ve encountered various database-related queries in my previous articles. In this article, we’ll dive into a specific question from Stack Overflow and explore how to identify suppliers who only offer trucks and computers.

Understanding the Problem Statement

The original poster is working with a database that contains information about suppliers, products, and offers. They have a query that identifies suppliers who offer both computers and trucks, but they want to refine their search to find suppliers who only offer these two specific products and nothing else.

Database Schema Overview

To understand the problem statement better, let’s take a look at the database schema:

• Tb_Supplier: This table contains information about each supplier.
• Tb_Product: This table contains information about each product offered by suppliers.
• Tb_Offers: This table contains information about each offer made by a supplier for a specific product.

The relationships between these tables can be summarized as follows:

• A supplier can make multiple offers (one-to-many).
• An offer is associated with one product (many-to-one).
• A product can have multiple suppliers offering it (many-to-many).

Original Query Analysis

The original query provided in the Stack Overflow question aims to find suppliers who only offer computers and trucks:

SELECT Name
FROM Tb_Supplier
WHERE Supp_ID IN (
  SELECT Supp_ID
  FROM Tb_Offers, Tb_Product
  WHERE Tb_Offers.Prod_ID = Tb_Product.Prod_ID
  AND (Tb_Product.Name = 'Computer' OR Tb_Product.Name = 'Truck')
) AND Supp_ID NOT IN (
  SELECT Supp_ID
  FROM Tb_Offers, Tb_Product
  WHERE Tb_offers.Prod_ID = Tb_Product.Prod_ID
  AND (Tb_Product.Name != 'Computer' OR Tb_Product.Name != 'Truck')
)

This query works by first finding suppliers who offer both computers and trucks. However, it does not correctly identify suppliers who only offer these two products.

Improved Query Solution

The improved solution provided in the answer uses a common table expression (CTE) to simplify the query:

WITH dat AS (
  SELECT 'Toronto' Supp_id,'Computer' thename UNION ALL
  SELECT 'Toronto' Supp_id,'Truck' thename UNION ALL
  SELECT 'Toronto' Supp_id,'Furniture' thename UNION ALL
  SELECT 'Ottawa' Supp_id,'Computer' thename UNION ALL
  SELECT 'Ottawa' Supp_id,'Truck' thename UNION ALL
  SELECT 'Montana' Supp_id,'Furniture' thename UNION ALL
  SELECT 'Idaho' Supp_id,'Computer' thename UNION ALL
  SELECT 'John' Supp_id,'Truck' thename UNION ALL
  SELECT 'John' Supp_id,'Furniture' thename UNION ALL
  SELECT 'Boris' Supp_id,'Computer' thename UNION ALL
  SELECT 'Boris' Supp_id,'Truck' thename UNION ALL
  SELECT 'Harold' Supp_id,'Furniture' thename UNION ALL
  SELECT 'Yelson' Supp_id,'Furniture' thename
)
SELECT Supp_ID from dat where thename = 'Computer'
INTERSECT 
SELECT Supp_ID from dat where thename = 'Truck'
EXCEPT
SELECT Supp_ID from dat where thename not in ('Truck','Computer')

This query works by first generating a table with all possible combinations of suppliers and products. It then finds the intersection (suppliers who offer both computers and trucks) and excludes those who offer any other product.

How it Works

Let’s break down how this improved query works:

• The WITH dat AS ... part defines a temporary result set called dat. This is where we generate all possible combinations of suppliers and products.
• We use the UNION ALL operator to combine rows from multiple queries, effectively creating a table with one row per supplier-product combination.
• We select suppliers who offer both computers ('Computer') and trucks ('Truck') using the INTERSECT operator. This returns a set of suppliers who have both products in their offerings.
• To exclude suppliers who offer any other product, we use the EXCEPT operator. We select all suppliers from the dat table where neither 'Computer' nor 'Truck' is present (thename not in ('Truck','Computer')). These are suppliers who do not meet our criteria.
• By intersecting and excluding these sets, we find suppliers who only offer trucks and computers.

Conclusion

In this article, we’ve explored a problem statement from Stack Overflow where the goal was to identify suppliers who only offer trucks and computers. We analyzed an original query provided in the question and identified its flaws. The improved solution using a CTE provides a clear and concise way to achieve our goal.

By understanding how to use database concepts like intersections, unions, and exclusions, we can write more efficient queries to solve complex problems in our data analysis tasks.

Last modified on 2023-11-20