|
If our task is to retrive pieces of information stored
in different tables using single query statement, we will use join
between two or more tables. Let's see example where we want to retrive
Departemnt name in addition to the information stored in
Employees table. At first we pick up two tables, give them aliases
and chose certain fields from each table. After this we specify Join condition
in WHERE clause, that in our case will be based on the value of DeptNo
column.
SELECT a.EmpNo, a.DeptNo, a.FirstName, a.LastName, a.Gender, b.DeptName AS Departement
FROM Employees AS a, Departments AS b
WHERE a.DeptNo = b.DeptNo
|
This query when executed will return result like this:
| EmpNo | FirstName | LastName | Gender | Departement |
| 1 | john | silver | M | management |
| 2 | gret | garbo | F | management |
| 3 | le | chen | M | logistic |
| 14 | suzan | shpeer | F | logistic |
| 4 | daniel | defo | M | advertizing |
| .. | .. | .. | .. | .. |
The possible way to retriave comlpete list of columns is to use sighn [*] instead
of specific names. For example, if want Departement name, and ALL columns from
Employees table:
SELECT b.DeptName AS Departement, a.*
FROM Employees AS a, Departments AS b
WHERE a.DeptNo = b.DeptNo
|
Result will look like this:
| Departement | EmpNo | DeptNo | FirstName | LastName | Gender | city | ... |
| management | 1 | 1 | john | silver | M | london | ... |
| management | 2 | 1 | gret | garbo | F | berlin | ... |
| logistic | 3 | 2 | le | chen | M | berlin | ... |
| logistic | 14 | 2 | suzan | shpeer | F | berlin | ... |
| advertizing | 4 | 3 | daniel | defo | M | rome | ... |
In many DBMS systems there is a possibilty to save retrieved data set into a new
table. In MS-Access you just need to add INTO clause before FROM. The newlly
created table will inherit datatypes from the source tables involved:
SELECT b.DeptName AS Departement, a.* INTO Employees_New
FROM Employees AS a, Departments AS b
WHERE a.DeptNo = b.DeptNo
|
|