laika222
11/3/2016 - 9:44 PM

You can add multiple JOINs to join columns from many tables. The shared value that links the table doesn't need to be present in all tables

You can add multiple JOINs to join columns from many tables. The shared value that links the table doesn't need to be present in all tables - rather, there needs to just be a shared link in-between each pair of tables, but that link can change between pairs of tables. Line 1 selects the various columns you want to pull. Line 2 tells it to pull FROM the Orders1 table. Lines 3-4 say to JOIN from the Customers1 table, with the link between the two tables being the CustomerID being equal in both tables. Lines 5-6 say to JOIN from the Products1 table, with the link between the two tables being the ProductID being equal in both tables. Lines 7-8 say to JOIN from the Companies1 table, with the link between the two tables being the CompanyID being equal in both tables. The results is that you can pull OrderID, CustomerID, Customer Last Name, Customer First Name, Product Name, Product Price, and Company Name into the results, even though the data resides in three separate tables.

SELECT Orders1.OrderID, Customers1.CustomerID, Customers1.LastName, Customers1.FirstName, Products1.Product, Products1.Price, Companies1.CompanyName
FROM Orders1
JOIN Customers1
  ON Orders1.CustomerID = Customers1.CustomerID
JOIN Products1
  ON Orders1.ProductID = Products1.ProductID
JOIN Companies1
  ON Products1.CompanyID = Companies1.CompanyID
ORDER BY Orders1.OrderID;

-- EXAMPLE OF HOW TO PULL INFO FROM customers1 TABLE FOR PEOPLE WHO HAVE CERTAIN VALUES IN THE orders1 TABLE AND THE products1 TABLE (DETAIL STARTS ON LINE 19)
SELECT * from customers1
JOIN orders1
ON customers1.CustomerID=orders1.CustomerID
JOIN products1 
ON products1.ProductID=orders1.ProductID

-- DETAIL EXAMPLE OF HOW TO PULL INFO FROM customers1 TABLE FOR PEOPLE WHO HAVE CERTAIN VALUES IN THE orders1 TABLE AND THE products1 TABLE 

-- SELECT ALL ROWS FROM customers1 TABLE
SELECT * from customers1

-- ADD IN orders1 TABLE
JOIN orders1

-- SELECT ONLY PEOPLE WHO HAVE SAME VALUE for CustomerID COLUMN IN BOTH customers1 TABLE AND orders1 TABLE (IN OTHER WORDS, YOU GET ONLY PEOPLE FROM customers1 TABLE WHOSE CustomerID ALSO SHOWS UP THE IN orders1 TABLE)
ON customers1.CustomerID=orders1.CustomerID

-- ADD IN products1 TABLE
JOIN products1 

-- SELECT ONLY PEOPLE WHO HAVE SAME VALUE for ProductID COLUMN IN BOTH products1 TABLE AND orders1 TABLE (IN OTHER WORDS, YOU GET ONLY PEOPLE FROM products1 TABLE WHOSE Product ID ALSO SHOWS UP THE IN orders1 TABLE, WHO IN TURNS ALSO SHOWS UP WITH SAME CustomerID in the customers1 TABLE)
ON products1.ProductID=orders1.ProductID

-- FINALLY, FILTER DOWN TO ONLY SELECT PEOPLE WHO HAVE A Product IN THE products1 TABLE WITH VALUE OF 'guitar. NOTE THE WHERE CLAUSE COMES AFTER THE ON CLAUSE.
WHERE products1.Product = 'guitar';

-- THE END RESULT IS IT TAKES customers1 TABLE, THEN LINKS TO THE orders1 TABLE AND FILTERS IT DOWN TO ONLY INCLUDE PEOPLE WHO SHOW UP WITH THE SAME CustomerID in the orders1 TABLE, THEN LINKS TO THE products1 TABLE AND FILTERS IT DOWN TO ONLY INCLUDE PEOPLE WHO SHOW UP WITH THE SAME ProductID in the products1 TABLE, AND FINALLY FILTERS IT DOWN SO IT ONLY SHOWS PEOPLE WHO HAVE A Product WITH A VALUE OF 'guitar'