CODEWITHSUNDEEP
mysqlsql
Vinoth Babu
Vinoth Babu

Reputation: 6852

My SQL Not Equal Not Working

I am trying to get data from table1 comparing its field with table2 in which both values are not equal

eg:

news:
--
id, title
1   test
2   teeest
3   tet

newsseen:
id, newsId
1   1
2   2

Following is my query with !=,

SELECT n.id, n.title, n.CreatedOn FROM news n , newsseen ns WHERE n.id != ns.newsId group by n.id;

It returns all data from news table instead of only 3rd row

But the following query works correct if i check with =

SELECT n.id, n.title, n.CreatedOn FROM news n , newsseen ns WHERE n.id = ns.newsId group by n.id;

It returns only 1st 2 rows.

May i know why and can get a solution for that please?

Upvotes: 0

Views: 71

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

You looking for NOT EXISTS

SELECT n.id, n.title, n.CreatedOn 
FROM news n 
WHERE Not exists (select 1 from newsseen ns where n.id = ns.newsId )

In your first query, For the first Id in news table (ie) 1. According to your condition n.id != ns.newsId there is one record in newsseen table which is not equal (ie) newsId = 2 so ID=1 will be returned.

similarly for Id=2 in news table there is a non matching record (ie) newsId=1 is present in newsseen table so Id=2 will also be returned.

for Id=3 in news table both the records are not matching in newsseen so Id=3 will also be returned

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

It is doing the correct thing. What you want is to do a "set-based" equality and see if nothing matches. This requires a LEFT JOIN:

SELECT n.id, n.title, n.CreatedOn
FROM news n LEFT JOIN
     newsseen ns 
     ON n.id = ns.newsId 
WHERE ns.newsId IS NULL;

I see no reason for a GROUP BY.

And, I should add my usual: Never use commas in the FROM clause. Always use explicit, proper JOIN syntax.

(You can also readily express this with NOT EXISTS and NOT IN, but your approach suggests LEFT JOIN. The three should have similar performance.)

Upvotes: 1

Related Questions