473,381 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 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
  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 3627
Banfa
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

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

Similar topics

2
by: jomonto | last post by:
Hello - I need to run a report or query that returns a family record with associated reason codes - as one record. For instance: FamilyID -1800 has three reasons for needing service (SP, WK, ED)...
4
by: Apple | last post by:
I have to create a query with many to many relationship, but I can't break it into 2 x 1 to many, should there anyone can teach me how to solve this problem. Thanks in advance!
3
by: Carmela | last post by:
Hello Readers, I am trying to update data on a form that is based on a query having a many-to-one-to-many relationship. I found this exciting link that seemed to have the solution for me: ...
3
by: lorirobn | last post by:
Hello, I have a report which uses a subreport. When I run the report, I get "Enter Parameter Value" error message for "tblGuestRoom". I click ok and the report seems to work fine. I...
5
by: CC | last post by:
Dear, I have a form that is opened using a sql statement: Part of the table structure: ----------------------------------------- TbGeneral: PK - dsr_id TbDossierSample:
2
by: john | last post by:
This must be simple but I can't figure it out. Table 1 1xM to table 2: Table 1 IDfield 1 2 Table 2 IDfield CriteriaField 1 Criteria value 1 2 Criteria value 1 2 ...
1
by: thepresidentis | last post by:
here is my problem, i have a website that compiles concert listings for users to search for shows in their local area, i ran into a problem with returning search results due to an improper format...
2
by: jl2886 | last post by:
I have a 2 tables. They are linked together by the primary key of the (one) table and one of the primary keys of the (many) table. When I bring the two tables in the query I get separate entries...
10
klarae99
by: klarae99 | last post by:
I am working on an Inventory Database in Access 2003. I am having trouble with a report I designed to show current inventory in stock. I have a form (frmInventory) that is unbound. There are four...
2
by: James0192 | last post by:
Hi there. I am attempting to create a simple access database to store contact information of various contractors. Each contractor provides a different service/trade (carpentry, electrical etc.)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.