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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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