Asad Mahmood Asad Mahmood - 2 months ago 12
SQL Question

How to show rows with empty values?

I am trying to figure out how to show rows with empty values with the "JobTitleID" Column.

SQL FILE:

DROP TABLE Employee;
DROP TABLE JobTitle;


-- create our table
CREATE TABLE JobTitle
(
JobTitleID Number(38) PRIMARY KEY,
JobTitle VARCHAR2(25)
)
;


CREATE TABLE Employee
(
EmployeeID Number(38) PRIMARY KEY,
EmployeeFName VARCHAR2(25),
EmployeeLName VARCHAR2(25),
ClientName VARCHAR2(25),
HoursWorked Number(5,2),
ChargeRate Number(5,2),
JobTitleID Number(38),
CONSTRAINT fkJobTitle FOREIGN KEY (JobTitleID) REFERENCES JobTitle(JobTitleID)
)
;



-- populate tables

INSERT ALL
INTO JobTitle (JobTitleID, JobTitle) VALUES (1,'Project Manager')
INTO JobTitle (JobTitleID, JobTitle) VALUES (2,'Programmer')
INTO JobTitle (JobTitleID, JobTitle) VALUES (3,'Network Specialist')
INTO JobTitle (JobTitleID, JobTitle) VALUES (4,'Technical Support')
INTO JobTitle (JobTitleID, JobTitle) VALUES (5,'Cable Installer')
INTO JobTitle (JobTitleID, JobTitle) VALUES (6,'DBA')
INTO JobTitle (JobTitleID, JobTitle) VALUES (7,'Telecom Engineer')
SELECT * FROM dual;

INSERT ALL
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (1,'Bob','Smith','Acme Corp.',125.5,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(2,'Bob','Smith','Astro Electric',32,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(3,'Jane','Doe','Acme Corp.',160,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(4,'Jane','Doe','Astro Electric.',220,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (5,'Jane','Doe','Durham Tiles',12,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (6,'Henry','Jones','Lighting Unlimited',245,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(7,'Jane','Fonda','Whale Mart',275,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(8,'Bill','Murray','Whale Mart',145,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(9,'Bill','Murray','ABC Logistics',45,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (10,'John','Jameson','Whale Mart',160,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (11,'John','Jameson','ABC Logistics',130,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(12,'John','Jameson','ABM Systems',8,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(13,'Homer','Simpson','Flitter',345,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(14,'John','Carpenter','Speedy Messengers',25,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (15,'John','Carpenter','Flitter',123,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (16,'John','Carpenter','ABM Systems',67,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(17,'John','Carpenter','ABC Logistics',23,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(18,'Mary','Jane','ABM Systems',43,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(19,'Mary','Jane','Flitter',156,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (20,'Frank','Herbert','Acme Corp.',55,45,3)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (21,'Kathy','Smith','',0,45,3)
SELECT * FROM dual;



COMMIT;


So far I have this:

COLUMN Employee FORMAT a25;
SELECT JOBTITLE.JOBTITLE AS "Job Title"
FROM EMPLOYEE
FULL JOIN JOBTITLE
ON EMPLOYEE.JOBTITLEID = JOBTITLE.JOBTITLEID
WHERE EMPLOYEE.JOBTITLEID = 0
ORDER BY "Job Title"; /*Ordering it by alphabetical order/*


Now I know that
WHERE EMPLOYEE.JOBTITLEID = 0
won't work because of the table
JobTitle
Not having 0 as a Valid
JobTitleID
.

The ouput that I am seeking is:

Job Title
-----------------------
DBA
Telecom Engineer


Because of those rows not having any "Employees".

I am currently getting no rows shown.

vkp vkp
Answer

You can use a left join on the jobtitle table.

SELECT DISTINCT JOBTITLE.JOBTITLE AS "Job Title"
FROM JOBTITLE 
LEFT JOIN EMPLOYEE
ON EMPLOYEE.JOBTITLEID = JOBTITLE.JOBTITLEID
WHERE EMPLOYEE.JOBTITLEID IS NULL
ORDER BY "Job Title";

or using not exists.

select jobtitle 
from jobtitle j
where not exists (select 1 from employee where jobtitleid = j.jobtitleid)
order by 1
Comments