How formulate a SQL query that needs to mention four different tables?

I want to get the Company name and all the products they purchased in a single SQL query.

The database used is the Northwind sample database. The relations between these columns and dbs’ tables look like this:

ProductName <- [T]Products     -> [K]ProductID
                                       /
                                       ||
                                       /
               [T]OrderDetails -> [K]ProductID
                               .. [K]OrderID
                                       /
                                       ||
                                       /
               [T]Orders       -> [K]OrderID
                               .. [K]CustomerID
                                       /
                                       ||
                                       /
CompanyName <- [T]Customers    -> [K]CustomerID

Where [T] is a table and [K] are the table’s keys.

I tried to get at least the corresponding Product name, but SQL Server says that such inclusion is invalid.

SELECT ProductName
  FROM Products
 WHERE ProductID IN (SELECT ProductID
                       FROM [Order Details]
                      WHERE OrderID IN (SELECT OrderID, CustomerID
                                          FROM Orders));

How do I mention four different tables in one query?

Answer

You seem to be looking for joins:

select distinct c.companyName, p.productName
from customers c
inner join orders o on o.customerID = c.customerID
inner join orderdetails od on od.orderID = o.orderID
inner join products p on p.productID = od.productID

This gives, for each company, the list of all (distinct) products that were purchased by its customers.

Advertisement

Answer