## 7/08/2013

### Use a JOIN-ON function to retrieve a data record from related tables

You can pull data from two tables in a relational database using a JOIN function, but each of the tables must have a column of data that is a shared element -- a key in one table is the foreign key in another.

Let's pretend we have a company called Mythological Creatures.

There are two tables that help us keep track of our sales. The foreign key here would be Transactions.itemId, and the key would be Items.id:

SELECT * FROM transactions;

 Transactions id date itemId howMany totalCost 1001 2013-06-29 6 2 400 1002 2013-06-29 5 1 900 1003 2013-06-30 8 5 1500 1004 2013-06-30 5 2 2400 1005 2013-07-01 7 3 2100

SELECT * FROM Items;

 Items id nameOfItem cost 5 Unicorn 1200 6 Harpy 200 7 Chimera 800 8 Minotaur 300

----------------------------------------------------------------

QUESTION #1: How can I see all of our sales?

SOLUTION:

SELECT Transactions.*, Items.*
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id

iddateitemIdhowManytotalCostidnameOfItemcost
10012013-06-29624006Harpy200
10022013-06-29519005Unicorn1200
10032013-06-308515008Minotaur300
10042013-06-305224005Unicorn1200
10052013-07-017321007Chimera800

QUESTION #2: Which creatures did we sell on June 29?

SOLUTION:

SELECT nameOfItem, howMany
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id
WHERE date="2013-06-29";

nameOfItemhowMany
Harpy2
Unicorn1

QUESTION #3: How many creatures have we sold, totaled by each type?

SOLUTION:

SELECT nameOfItem, SUM(howMany) AS totalSold
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id
GROUP BY Items.id

nameOfItemtotalSold
Unicorn3
Harpy2
Chimera3
Minotaur5

QUESTION #4: What is our most profitable type of creature?

SOLUTION:

SELECT nameOfItem, SUM(totalCost) AS totalProfit
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id
GROUP BY Items.id
ORDER BY totalProfit DESC;

nameOfItemtotalProfit
Unicorn3300
Chimera2100
Minotaur1500
Harpy400

QUESTION #5: Which transactions have inconsistent sales figures?

SOLUTION:

SELECT Transactions.*, nameOfItem, (howMany * cost) AS shouldHaveBeen, (totalCost - (howMany * cost)) AS difference
FROM Transactions
JOIN Items
ON Transactions.itemID = Items.id
WHERE Transactions.totalCost <> (howMany * cost)
GROUP BY Transactions.id

iddateitemIdhowManytotalCostnameOfItemshouldHaveBeendifference
10022013-06-2951900Unicorn1200-300
10052013-07-01732100Chimera2400-300