Not equal to operator

Question

Not equal to operator

sudrav

SSC-Addicted

Points: 424
More actions
September 15, 2008 at 12:47 pm

#198156

This may sound like a very basic query. But I am sure I am missing something somewhere.
1. There are two databases. One is the main patient table and the other is a disease related table. I am trying to filter patients who do not have any disease. Sounds straight forward. The key is an id in the format 111111111 in the main patient table and in the other table it’s in the format 111-111-111. So I did a substring comparison to match them both. Here is my query
Select Name, Key, ID From database 1 db1
Join database 2 db2
On left(db1.ID,3) + SubString(db1,5,2) + SubString (db1,8,4) != db2.IdNum
Order by Key
Can anyone tell me what is wrong
2.The next thing. From the list I get I want to pick a random 5% of the total records retrieved. Can someone tell me how to do that ?
Thanks for all the help.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's not really enough information to answer the first question. Could you post the schema of the two tables and some sample data please?

Try using replace instead of the substrings, so REPLACE(db1.ID, '-','')

For the second, do you need exactly 5% and how random do the records need to be?

If the answer is no and not very, look up the TABLESAMPLE keyword. It's used in the FROM of a query.

If you need exactly 5% and quite random then you could use

SELECT TOP (5) PERCENT

...

ORDER BY NEWID()

it won't be a fast query if there's a lot of rows

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sudrav SSC-Addicted Points: 424 More actions · Answer 2

Thanks.It's basically a SSN number and in one table it is 111-11-1111 and in the other its 111111111. The query works if I use the equal to operator

Select Name, Key, ID From database 1 db1

Join database 2 db2

On left(db1.ID,3) + SubString(db1,5,2) + SubString (db1,8,4) = db2.IdNum

Order by Key

Any help is appreciated.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

If your query works what's the question?

Edit:Never Mind I can't read... You could use a Left join with NULL in the Where Clause to get the patients who don't have a disease.

--Patients

CREATE TABLE #tbl1 (

Col1 VARCHAR(11)

)

INSERT INTO #tbl1 select '111-111-111'

INSERT INTO #tbl1 select '123-456-789'

--Pats with Diseases

CREATE TABLE #tbl2 (

Col1 INT)

INSERT INTO #tbl2 select '111111111'

--Verify the data IS there

SELECT * FROM [#tbl1]

SELECT * FROM [#tbl2]

SELECT *

FROM #tbl1

LEFT JOIN [#tbl2]

ON LEFT([#tbl1].[Col1], 3) + SUBSTRING([#tbl1].[Col1], 5,3) + SUBSTRING([#tbl1].[Col1], 9, 3 ) = [#tbl2].[Col1]

WHERE [#tbl2].[Col1] IS NULL

--cleanup

DROP TABLE #tbl1

DROP TABLE #tbl2

To help us help you read this[/url]For better help with performance problems please read this[/url]

sudrav SSC-Addicted Points: 424 More actions · Answer 4

sudrav

SSC-Addicted

Points: 424

September 15, 2008 at 1:25 pm

#872008

The equal to works the not equal to does not work

sudrav SSC-Addicted Points: 424 More actions · Answer 5

sudrav

SSC-Addicted

Points: 424

September 15, 2008 at 1:27 pm

#872011

I want to filter based on not qual to

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Table schema and sample data please?

I suspect what you need is a NOT EXISTS rather than a !=. You're looking for patients where there are no matching rows in the disease table. The != will find you patients where any row in the disease table doesn't match. Since (I assume) there's multiple rows in the disease table, there will always be a row that doesn't match.

Something like this (rough)

SELECT name FROM patients

WHERE NOT EXISTS (SELECT 1 FROM diseases where disease.SSN = patients.SSN)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sudrav SSC-Addicted Points: 424 More actions · Answer 7

Database 1 : Field SSN: Data Type: varchar: Sample 111223333

Database 2: Filed SSN_2 Date Type: varchar: Sample 111-22-3333

Is this what u r looking for ?

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 8

Wow,

First off, build a new table for the database that has the 111-11-1111 format, with two columns, one with the original value, and the other with the new, cleaner format. (if this is possbile)

Then it is a simple query that uses a left outer join

select * from patient_table as p

left outer join disease_table as d on id=id

where d.id is null

This will return all patients that do not have a record in the disease table.

The more you are prepared, the less you need it.