Query not returning desired results - One to Many relationship

[rhonda6373]

I am a beginner and trying to get the following results:

All records from page0 where records on page4 do not have a Role of 'DCA Specialist'. Page0 is the one record table and Page4 is the table with the many records.

The issue is:
I getting records that have 'DCA Specialist' on page4. The Serial Number in Page0 joins to the CaseSerial in Page4.

Here is the query I attempted to do:

SELECT distinct dbo.PAGE0.MatterNo, dbo.PAGE0.MatterNm, dbo.PAGE0.SubType
FROM   dbo.PAGE0 INNER JOIN
             dbo.PAGE4 ON dbo.PAGE0.SERIAL = dbo.PAGE4.CaseSerial
WHERE (dbo.PAGE4.Role <> 'DCA Specialist') AND (dbo.PAGE0.SubType = 'DCA Files') and page0.status = 'Open'

[Banfa]

What is the type of your columns specifically dbo.PAGE4.role because the <> operator cannot work on all column types, specifically TEXT, NTEXT and IMAGE ( https://docs.microsoft.com/en-us/sql...l-server-ver15 )

If that isn't the issue are you sure the role is spelt correctly in all records i.e. if it was 'DCA Specialest' it would be not equal; are you sure there is no trailing space i.e. if it was 'DCA Specialist ' it would be not equal.

If you are use text to make matches you need to make sure the text is the same everywhere. Sometimes you see people putting role names into separate tables and use the ids from that table everywhere else so that the string only appears once. Some rdbs systems have an ENUM type to cover this situation but not TSQL.