CODEWITHSUNDEEP
mysql
raja
raja

Reputation: 35

mysql query handling repetation

I'm having a problem with a MySQL query.
I need to display the following output:

id | name  |     leave_date
-------------
1    sam         2011-11-22
2    david       2011-11-23
3    cooper      2011-11-27
4    sam         2011-11-30

My query is 

select * from table1 where leave_date != "2011-11-22"

NOTE: YOU SHOULD NOT USE DIRECTLY THE "NAME".. I NEED TO ELIMINATE THE REPETITION

I get output like this:

id | name  |     leave_date
-------------
2    david       2011-11-23
3    cooper      2011-11-27
4    sam         2011-11-30

But I want to eliminate "sam", so output would be:

id | name  |     leave_date
-------------
2    david       2011-11-23
3    cooper      2011-11-27

Upvotes: 0

Views: 60

Answers (4)

勿绮语
勿绮语

Reputation: 9330

select * from table1 where name not in
(select name from table1 group by name having count(*) > 1)

Upvotes: 1

Bryan
Bryan

Reputation: 2211

I think this is what you are looking for. If anyone has a prettier way, feel free to edit: 

SELECT * from table1 WHERE leave_date != "2011-11-22" 
AND name NOT IN (SELECT name from table1 WHERE leave_date = "2011-11-22")

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26861

You want to eliminate sam record based on what logic?

Not having a business logic condition based on what to eliminate sam, the only way to do it is similar to:

select * from table1 where leave_date!="2011-11-22" and name <> 'sam'

Upvotes: 0

Zohaib
Zohaib

Reputation: 7116

but i want to eliminate "sam" and output would be 

select * 
from table1
where name <> 'sam'

Upvotes: 0

Related Questions