jacob-on-stackoverflow jacob-on-stackoverflow - 6 months ago 10
SQL Question

How to Organize Multiple Joins SQL

In SQL, how should I be joining tables together when I do multiple joins in one query. Should I join on only one table - in this case the Customers table or is it okay to do what I have done (joining on different tables as new keys are needed)?

SELECT O.OrderID, O.OrderDate, C.City, C.Country, C.PostalCode, C.ContactName, O.CustomerID, O.ShipperID, D.ProductID, COUNT(D.ProductID) ProductCount, S.SupplierID
FROM Customers C

INNER JOIN Orders O
ON O.CustomerID = C.CustomerID
INNER JOIN OrderDetails D
ON O.OrderID = D.OrderID
INNER JOIN Products P
ON D.ProductID = P.ProductID
INNER JOIN Suppliers S
ON S.SupplierID = P.SupplierID

WHERE 1 = 1
GROUP BY O.OrderID
ORDER BY OrderDate DESC


I am using W3Schools SQL TryIt editor to test this, not sure what DB engine it is!

Thanks!

Answer

Of course you can join on multiple tables in a query. That is a big part of the power of SQL.

In your particular case, you don't need the join to the Suppliers table, because the column is already in Products.

Also, you need to be careful about your SELECT and GROUP BY clauses. In general, you should put all non-aggregated columns in the GROUP BY:

SELECT O.OrderID, O.OrderDate, C.City, C.Country, C.PostalCode, C.ContactName, 
       O.CustomerID, O.ShipperID, D.ProductID,
       COUNT(D.ProductID) as ProductCount,
       P.SupplierID
FROM Customers C INNER JOIN
     Orders O 
     ON O.CustomerID = C.CustomerID INNER JOIN
     OrderDetails D 
     ON O.OrderID = D.OrderID INNER JOIN
     Products P
     ON D.ProductID = P.ProductID
GROUP BY O.OrderID, O.OrderDate, C.City, C.Country, C.PostalCode, C.ContactName, 
       O.CustomerID, O.ShipperID, D.ProductID, P.SupplierId
ORDER BY OrderDate DESC;

The WHERE 1=1 is also unnecessary.

I wonder if this query really does what you want. However, you don't state what you actually want the query to do, so I'm merely speculating.