SQL: How to SELECT data from two tables, using JOIN

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

id	date	itemId	howMany	totalCost	id	nameOfItem	cost
1001	2013-06-29	6	2	400	6	Harpy	200
1002	2013-06-29	5	1	900	5	Unicorn	1200
1003	2013-06-30	8	5	1500	8	Minotaur	300
1004	2013-06-30	5	2	2400	5	Unicorn	1200
1005	2013-07-01	7	3	2100	7	Chimera	800

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";

nameOfItem	howMany
Harpy	2
Unicorn	1

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

nameOfItem	totalSold
Unicorn	3
Harpy	2
Chimera	3
Minotaur	5

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;

nameOfItem	totalProfit
Unicorn	3300
Chimera	2100
Minotaur	1500
Harpy	400

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

id	date	itemId	howMany	totalCost	nameOfItem	shouldHaveBeen	difference
1002	2013-06-29	5	1	900	Unicorn	1200	-300
1005	2013-07-01	7	3	2100	Chimera	2400	-300