!= NOT EQUAL TO Query

Question

Post reply

!= NOT EQUAL TO Query

jbulldog

SSC Veteran

Points: 241
More actions
October 25, 2012 at 7:23 pm

#264642

I am trying to do a Not Equal To Query. I have multiple tables with numerous columns. All of the records needed are in two tables.
I am doing an INNER JOIN with a WHERE clause to filter for the records I need. I want to filter and list for the records that do not
have ' BENEFITS N/A' in the field(s) for the column Db.Services.Type.
Here are my Tables: Db.dbase and Db.Services
These are my Columns:
(Db.dbase.name, Db.dbase.ID_Num, Db.dbase.ID_Type,Db.dbase.GroupName, Db.dbase.Region, Db.dbase.Status, Db.Services.Type)
My Code on SQL Server 2008:
SELECT Db.dbase.name, Db.dbase.ID_Num, Db.dbase.ID_Type, Db.dbase.GroupName, Db.dbase.Region
FROM Db.dbase INNER JOIN Db.Services ON Db.dbase.ROW_ID = Db.Services.ROW_ID
WHERE Db.dbase.Region IN ('USA') AND (Db.dbase.Status = 'Active' OR dbo.Db.dbase.Status = 'Inactive') AND
Db.Services.Type != (' BENEFITS N/A')
Appreciate any help with proper code,format and some kinda resolution. Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Looks like you have a leading space in ' BENEFITS N/A'

The query is working fine (probably) 🙂

Clear Sky SQL
My Blog[/url]

BriPan SSCrazy Points: 2061 More actions · Answer 2

it seems fine from my side too.

can you please share some data and alse if possible table structure(involved in query)

so that have good visibility on query and can give proper answer.

Lowell SSC Guru Points: 323518 More actions · Answer 3

I think the core issue is parenthesis.

Because of the other two conditions the results still have the values he was trying to filter out.

and/and/or needs to be handled ,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jbulldog SSC Veteran Points: 241 More actions · Answer 4

Thanks all the parenthesis don't seem to have anything to do with it I'm thinking it's the "!=", Query runs successful but does not return any records.

Tried "IS NOT" in lieu of "!=" and got syntax error message with or with out ().

What's stopping it from pulling those records where this field data is missing?

Thanks again for all the advice.

BriPan SSCrazy Points: 2061 More actions · Answer 5

"Is Not" will not going to work you have to use != or <>

looking to your query it seems now problem is in and or combination you have used

still i suggest share some data so that can check and give perfact result

else

try to execute query without where clause check result

then put one by one where clauses in your query which help you to debug your own query

jbulldog SSC Veteran Points: 241 More actions · Answer 6

As fars properties go this is as much as I have access to:

TABLE_QUALIFIER

Db.dproperties

TABLE_OWNER

Db

TABLE_NAMES

Db.dbase

Db.Services

COLUMN_NAMES

name

ID_num

ID_Type

GroupName

Region

ROW_ID

Type

ROW_ID

(varchar(100), null)

(varchar(25), null)

(varchar(15), null)

(varchar(100), null)

(varchar(15), null)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Comment out the filter you aren't sure about:

SELECT Db.dbase.name, Db.dbase.ID_Num, Db.dbase.ID_Type, Db.dbase.GroupName, Db.dbase.Region

FROM Db.dbase INNER JOIN Db.Services ON Db.dbase.ROW_ID = Db.Services.ROW_ID

WHERE Db.dbase.Region IN ('USA') AND (Db.dbase.Status = 'Active' OR dbo.Db.dbase.Status = 'Inactive') --AND

--Db.Services.Type != (' BENEFITS N/A')

and order the result set by Db.Services.Type so you can eyeball the results.

Edit: hanging AND commented out too.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jbulldog SSC Veteran Points: 241 More actions · Answer 8

When I INNER JOIN all my tables (6) I pull all the records for a specific region which already have the field data I am querying to see is missing..

Lowell SSC Guru Points: 323518 More actions · Answer 9

jbulldog (10/26/2012)
When I INNER JOIN all my tables (6) I pull all the records for a specific region which already have the field data I am querying to see is missing..

change your inner joins to left outer joinsd.

inner join would only show when data exists in all 6 tables.

"missing data', ie data that's not in one of the tables, but should be, will require outer joins to see them.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jbulldog SSC Veteran Points: 241 More actions · Answer 10

Thanks Lowell, I tried the LEFT OUTER JOIN for both instances. First with the same code I posted, no records returned with successful query. Then with all tables referenced using LEFT OUTER JOIN, which pulled all records again. Something is missing.

jbulldog SSC Veteran Points: 241 More actions · Answer 11

Should I use filter criteria with the NULL for this field if so how.