Hi guys
Consider the two tables below. Assuming there was no relationship between the two tables how would one create a sp that returns all employees that have a salarylevelID that is not in the salary level table? Any ideas?
Thanks all you wonderful people!
TABLE [dbo].[Employees](
[employeeNo] [int] IDENTITY(1,1) NOT NULL,
[lastName] [varchar](50) NOT NULL,
[firstName] [varchar](50) NOT NULL,
[gender] [char](1) NOT NULL,
[IDNumber] [varchar](20) NOT NULL,
[salaryLevelID] [int] NULL,
[departmentID] [int] NULL,
TABLE [dbo].[salaryLevel](
[salaryLevelID] [int] IDENTITY(1,1) NOT NULL,
[amount] [decimal](7, 2) NULL,
[increasePercentage] [smallint] NULL,
I'd use NOT EXISTS probably
SELECT e.EmployeeNo
FROM dbo.Employees AS e
WHERE NOT EXISTS(
SELECT sl.SalaryLevelID
FROM dbo.SalaryLevel AS sl
WHERE sl.SalaryLevelID = e.SalaryLevelID);
There are other ways as well. An outer join & filter on NULL values would also probably work. OUTER CROSS APPLY also filtered on NULL. Might be others.
I'd strongly suggest getting the relationship in place. Foreign keys serve a real purpose and in fact enhance performance as well as avoid situations like this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2020 at 12:29 pm
Just a quick point on terminology. The 'relationship' is already there between the two tables.
What I think you are referring to is known as a 'constraint', specifically a 'foreign key' constraint.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply