SQL Query Display the names of the employees and their corresponding department

Question

The following are my CREATE TABLE scripts:

CREATE TABLE Person 
( 
   ID INT Primary Key Identity (1,1), 
   LastName nVarchar (20) not NULL, 
   FirstName nVarchar (20) not NULL, 
   MiddleName nVarchar (20), 
   BirthDate DateTime not NULL, 
   Age INT not NULL, 
   Check (Age18) 
); 

CREATE TABLE Department 
( 
   ID INT Primary Key Identity (1,1), 
   DepartmentName nVarchar (50) Unique, 
   DepartmentCode nVarchar (20) Unique, 
   IsActive Bit Default (1) 
); 

CREATE TABLE Employee 
( 
   ID INT Primary Key Identity (1,1), 
   PersonId INT Foreign Key REFERENCES Person, 
   DepartmentId INT Foreign Key REFERENCES Department, 
   Salary Decimal (18,2), 
   Check (Salary10000), 
   IsActive Bit Default (1) 
);

The following are my INSERT scripts:

INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Dela Cruz','Juan',NULL,01/01/199,22) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Dela Cerna','Pedro','Juan',11/01/1993,21) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Villaflores','Rachel','Diacoma',10/7/1990,24) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Abendan','Marnell',NULL,03/15/1989,25) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Oplado','Aiza','Tapayan',11/18/1993,21) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Loreto','Desire','Talingting',06/10/1993,21) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Magbanua','Prince Laurence','Rallos',05/25/1992,22) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Locsin','Franz Cyril',NULL,02/14/1993,21) 
INSERT INTO Person (LastName, FirstName, MiddleName,BirthDate,Age) 
VALUES ('Dela Pena','Precious',NULL,01/01/199,21) 

 INSERT INTO Department (DepartmentName, DepartmentCode, IsActive) 
VALUES ('ITDepartment','ItDept',1) 
INSERT INTO Department (DepartmentName, DepartmentCode, IsActive) 
VALUES ('EngineeringDepartment','EDept',1) 
INSERT INTO Department (DepartmentName, DepartmentCode, IsActive) 
VALUES ('ComputerScienceDepartment','CSDept',1) 
INSERT INTO Department (DepartmentName, DepartmentCode, IsActive) 
VALUES ('InformationSystemDepartment','ISDept',1) 
INSERT INTO Department (DepartmentName, DepartmentCode, IsActive) 
VALUES ('BusinessAdministrationDepartment','BADept',1) 
INSERT INTO Department (DepartmentName, DepartmentCode, IsActive) 
VALUES ('ElementaryDepartment','ElemDept',1) 

 INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (1,2,12000,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (2,4,10001,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (3,6,13000,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (4,1,25000,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (5,3,15000,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (6,5,10002,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (7,1,56000,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (8,4,14000,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (9,6,15900,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (2,4,12300,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (4,1,13500,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (6,3,14300,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (8,5,12500,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (1,2,11460,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (3,4,10910,1) 
INSERT INTO Employee (PersonId,DepartmentId,Salary,IsActive) 
VALUES (5,6,10001,1)

The following is my select script:

SELECT 
    Person.LastName, Department.DepartmentName 
FROM 
    Person, Department 
FULL OUTER JOIN 
    Employee ON Employee.PersonId = Employee.DepartmentId

This is the result which is not right. I don't know what is the right thing for this. This is my first time doing JOINS.

Dela Cruz   BusinessAdministrationDepartment 
Dela Cerna  BusinessAdministrationDepartment 
Villaflores BusinessAdministrationDepartment 
Abendan BusinessAdministrationDepartment 
Oplado  BusinessAdministrationDepartment 
Loreto  BusinessAdministrationDepartment 
Magbanua    BusinessAdministrationDepartment 
Locsin  BusinessAdministrationDepartment 
Dela Pena   BusinessAdministrationDepartment 
Dela Cruz   ComputerScienceDepartment 
Dela Cerna  ComputerScienceDepartment 
Villaflores ComputerScienceDepartment 
Abendan ComputerScienceDepartment 
Oplado  ComputerScienceDepartment 
Loreto  ComputerScienceDepartment 
Magbanua    ComputerScienceDepartment 
Locsin  ComputerScienceDepartment 
Dela Pena   ComputerScienceDepartment

I stopped at computer science department because it will be too long. How do I get this right? Please help. Thanks! :)

check the syntax with some **recent** online site. the issue here is the mix of the old (as in 'on the way of the dodo') join syntax with the new join syntax and the missing of the basics: it is something that is way too long to address here. — Paolo, Jul 16 '15 at 09:48
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Jul 16 '15 at 09:57
Thanks for enlightening me. I would like to use. Let's say I wouldn't use the comma-separated list of tables. How would you do it using the proper ANSI JOIN syntax? Thanks :) — Jezun, Jul 16 '15 at 10:08

score 2 · Accepted Answer · answered Jul 16 '15 at 09:51

2

are you looking for this:-

Select  p.LastName, d.DepartmentName
From    Employee As e
        Join Person As p On e.PersonId = p.Id
        JOin Department As d On e.DepartmentId = d.ID

answered Jul 16 '15 at 09:51

Mihir Shah

948
10
17

Thanks a lot this totally helped me! I'm studying this one now! – Jezun Jul 16 '15 at 10:07

score 1 · Answer 2 · answered Jul 16 '15 at 09:58

1

SELECT Person.LastName, Department.DepartmentName FROM Employee INNER JOIN Person ON Employee.PersonId = Person.ID INNER JOIN Department ON Employee.DepartmentId = Department.ID

answered Jul 16 '15 at 09:58

rgstamayo

163
12

Thanks a lot this totally helped me! I'm studying this one now! – Jezun Jul 16 '15 at 10:07

SQL Query Display the names of the employees and their corresponding department

2 Answers2