471,088 Members | 1,285 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,088 software developers and data experts.

Query not returning desired results - One to Many relationship

35 32bit
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT distinct dbo.PAGE0.MatterNo, dbo.PAGE0.MatterNm, dbo.PAGE0.SubType
  3.              dbo.PAGE4 ON dbo.PAGE0.SERIAL = dbo.PAGE4.CaseSerial
  4. WHERE (dbo.PAGE4.Role <> 'DCA Specialist') AND (dbo.PAGE0.SubType = 'DCA Files') and page0.status = 'Open'
Jun 15 '21 #1
1 2549
9,065 Expert Mod 8TB
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.
Jun 15 '21 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by jomonto | last post: by
4 posts views Thread by Apple | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.