Mysql 6: JOIN
Written by: maffelu , 2009-04-16 21:19:21
JOIN:
If you want to join two tables in a database for some reason, this is supported. We will use an example where we will categorize our stores into their proper owners. There are two owners, MegaCorp and Superstore, and they have two stores each. Now, to see which one of these owners who makes the most money, we'll have to join the tables.
These will be our tables:
stores
|--------------------+--------------------+--------------------|
|store_name |store_sales |store_updates |
|____________________+____________________+____________________|
|Hanks Hardware 2500 2009-01-01 00:00:00 |
|Santos SuperStore 750 2008-12-01 00:00:00 |
|Bobs Boutique 1250 2009-01-01 00:00:00 |
|Pattyïs Pantstore 1050 2008-12-15 00:00:00 |
|Hanks Hardware 2250 2008-01-01 00:00:00 |
+--------------------------------------------------------------+
owners
|--------------------+--------------------|
|name |store |
|____________________+____________________|
|MegaCorp Bobs Boutique |
|MegaCorp Pattyïs Pantstore |
|SuperStores Hanks Hardware |
|SuperStores Santos SuperStore |
+-----------------------------------------+
We will want to compare the store_name column in Stores with the store column in owners. The query will look like this:
SELECT A1.name AS owner, SUM(A2.store_sales) AS Sales
FROM owners A1, stores A2
WHERE A1.store = A2.store_name
GROUP BY A1.name
|--------------------+--------------------|
|owner |Sales |
|____________________+____________________|
|MegaCorp 2300 |
|SuperStores 5500 |
+-----------------------------------------+
As we can see, we're also Using ALIAS to create a query that is easier to understand. Let's go through this one line by line:
SELECT A1.name AS owner, SUM(A2.store_sales) AS Sales
Here we use two columns, name and store_sales. Problem is, they belong to differant tables. So we use ALIAS to show they are from differant tables. This could also be written:
SELECT owners.name AS owner, SUM(stores.store_sales) AS Sales
What is means is that we select all records in the owner table column name AND the SUM of the store tables store_name column.
The second row is where we declare our source. Since we're using two tables in our select statement, we'll need to declare two tables here as well, A1 and A2!
FROM owners A1, stores A2
Then we split the data up into the owners stores:
WHERE A1.store = A2.store_name
And we group it as a finalé, it's optional, but you can get som funny results if you don't
There are no comments on this article.
Leave a message!
If you have any question or just want to leave a message, just fill out the form below!
Your e-mail will not be visible in your post, it is for validation reasons only
Maffelu