CODEWITHSUNDEEP
mysqlsql
user3286692
user3286692

Reputation: 383

How to make Select Query to suit null conditions and not null condition

select first_name,last_name from contacts where first_name = 'somevalue' and last_name = 'somevalue'

I get these values via ajax

Sample table contacts

id | first_name | last_name

1  | Rahul      |  Roy
2  | Rohit      |  NULL

Now when via ajax I get first_name as "Rahul" and last_name as "Roy" query returns 1 records (the first one)

But if I get first_name as "Rohit" and last_name is empty I get below query 

select first_name,last_name from contacts where first_name = 'Rohit' and last_name = ''

I get no records.But actually I want to get the record with id 2 from above table.

How can my query fullfill both conditions in just one query

Upvotes: 0

Views: 56

Answers (4)

akhattar
akhattar

Reputation: 13

select first_name,last_name 
from contacts 
where first_name = 'Rahul' and (last_name = 'Roy' or last_name is null);

Upvotes: 0

Piotrek
Piotrek

Reputation: 3

Use "is null" instead of "=''".

i.e.

select first_name,last_name from contacts where first_name = 'Rohit' and last_name is null

Upvotes: 0

Punitha Subramani
Punitha Subramani

Reputation: 1477

USE NULL for this condition, Can you try this?

select first_name,last_name 
from contacts 
where first_name = 'Rohit' and last_name = ''

to

select first_name,last_name 
from contacts 
where first_name = 'Rohit' 
      and (last_name IS NULL || last_name='somevalue')

If you want Last name not to be empty

select first_name,last_name 
from contacts 
where first_name = 'Rohit' 
      and last_name IS NOT NULL

Upvotes: 0

ah_hau
ah_hau

Reputation: 768

SELECT first_name,last_name 
FROM contacts 
WHERE first_name = 'Rohit' and (last_name IS NULL OR last_name = 'your value')

Here you go!

Upvotes: 3

Related Questions