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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply