Here we are going to use 3 Tables "Employees", "Department" and "Designation".
Below is shown the "Employees" table:
| 1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
343-123-2002 |
New Orleans |
950.00 |
| 2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
232-344-8755 |
San Francisco |
800.00 |
| 3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
435-657-9068 |
New York |
700.00 |
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
323-454-7656 |
Salt Lake City |
600.00 |
| 5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
634-465-8046 |
New York |
500.00 |
| 6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
434-434-1254 |
Houston |
400.00 |
| 7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
565-782-1223 |
Long Island |
650.00 |
| 8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
954-652-2111 |
Salt Lake City |
600.00 |
| 9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
323-243-1249 |
San Francisco |
750.00 |
| 10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
543-765-4653 |
Salt Lake City |
450.00 |
| 11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
443-324-6522 |
Austin |
500.00 |
Show Below is the "Department" table:
| 1 |
Finance |
| 2 |
Development |
| 3 |
Testing |
| 4 |
Delivery |
| 5 |
Human Resources |
| 6 |
Administration |
Below is shown the "Designation" table:
| 1 |
CEO |
| 2 |
CTO |
| 3 |
Program Manager |
| 4 |
Project Lead |
| 5 |
System Analyst |
| 6 |
Senior Software Engineer |
| 7 |
Test Manager |
| 8 |
Software Engineer |
| 9 |
Trainee Engineer |
| 10 |
Test Engineer |
SQL Using INNER JOIN
In the "Employees" table EmployeeID is the Primary Key. DeptID and DesignationID are Foreign Keys.
In the "Department" table DeptID is the Primary Key.
In the "Designation" table DesignationID is the Primary Key.
To display the Employee details along with Department Name and Designation.
Need to use the DeptID and DesignationID columns that are Foreign Keys in "Employees" table.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select Emp.EmployeeID, Emp.EmployeeName, Emp.DeptID,
(Select DeptName From Department where DeptID = Emp.DeptID) as DeptName,
Emp.DesignationId,
(Select Designation from Designation where Designation.DesignationID = Emp.DesignationID) as Designation
From Employees Emp;
Note: Emp.DeptID and Emp.DesignationID are passed as the arguments to the Subquery.