468,790 Members | 1,941 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,790 developers. It's quick & easy.

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
  2. FROM   dbo.PAGE0 INNER JOIN
  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 2379
Banfa
9,058 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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.