CODEWITHSUNDEEP
mysqlsqlif-statement
botenvouwer
botenvouwer

Reputation: 4432

If else on WHERE clause

I've this query:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE (
`email` LIKE  '%@domain.nl%'
OR  `email2` LIKE  '%@domain.nl%'
)

But I want to do something like this:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE IF(`email` > 0,
`email` LIKE  '%@domain.nl%'
,  `email2` LIKE  '%@domain.nl%'
)

How to check if email exist? I want to use email and if this field is empty I want to use email2. How do I accomplish this?

Upvotes: 55

Views: 194189

Answers (5)

Thanura Jayasekara
Thanura Jayasekara

Reputation: 82

Here is a sample query for a table having a foreign key relationship to the same table with a query parameter.

enter image description here

SET @x = -1;
SELECT id, categoryName 
FROM Catergory WHERE IF(@x > 0,category_ParentId = @x,category_ParentId IS NOT NULL);

@x can be changed.

Upvotes: 3

Ameen Maheen
Ameen Maheen

Reputation: 2737

try this ,hope it helps 

select user_display_image as user_image,
user_display_name as user_name,
invitee_phone,
(
 CASE 
    WHEN invitee_status=1 THEN "attending" 
    WHEN invitee_status=2 THEN "unsure" 
    WHEN invitee_status=3 THEN "declined" 
    WHEN invitee_status=0 THEN "notreviwed" END
) AS  invitee_status
 FROM your_tbl

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You want to use coalesce():

where coalesce(email, email2) like '%[email protected]%'

If you want to handle empty strings ('') versus NULL, a case works:

where (case when email is NULL or email = '' then email2 else email end) like '%[email protected]%'

And, if you are worried about the string really being just spaces:

where (case when email is NULL or ltrim(email) = '' then email2 else email end) like '%[email protected]%'

As an aside, the sample if statement is really saying "If email starts with a number larger than 0". This is because the comparison is to 0, a number. MySQL implicitly tries to convert the string to a number. So, '[email protected]' would fail, because the string would convert as 0. As would '[email protected]'. But, '[email protected]' and '[email protected]' would succeed.

Upvotes: 18

gen_Eric
gen_Eric

Reputation: 227230

IF is used to select the field, then the LIKE clause is placed after it:

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE IF(`email` != '', `email`, `email2`) LIKE  '%@domain.nl%'

Upvotes: 80

RB.
RB.

Reputation: 37182

Note the following is functionally different to Gordon Linoff's answer. His answer assumes that you want to use email2 if email is NULL. Mine assumes you want to use email2 if email is an empty-string. The correct answer will depend on your database (or you could perform a NULL check and an empty-string check - it all depends on what is appropriate for your database design).

SELECT  `id` ,  `naam` 
FROM  `klanten` 
WHERE `email` LIKE  '%[email protected]%'
OR (LENGTH(email) = 0 AND `email2` LIKE  '%[email protected]%')

Upvotes: 3

Related Questions