CODEWITHSUNDEEP
mysql
Barno
Barno

Reputation: 3331

If inside Where mysql

Can I do an if inside Where? or something that allows me to do the checks only if the field is not null (path=null)

SELECT  
IF(path IS NOT NULL, concat("/uploads/attachments/",path, "/thumbnails/" , nome), "/uploads/attachments/default/thumbnails/avatar.png") as avatar_mittente
FROM prof_foto  
WHERE profilo_id = 15 
 -- only if path != "/uploads/attachments/default/thumbnails/avatar.png"
AND foto_eliminata = 0 AND foto_profilo = 1

Upvotes: 0

Views: 166

Answers (3)

A. Zalonis
A. Zalonis

Reputation: 1609

You can try this

SELECT  
IF(path IS NOT NULL, concat("/uploads/attachments/",path, "/thumbnails/" , nome), "/uploads/attachments/default/thumbnails/avatar.png") as avatar_mittente
FROM prof_foto  
WHERE profilo_id = 15 
AND IF( path != "/uploads/attachments/default/thumbnails/avatar.png", 
         foto_eliminata = 0 AND foto_profilo = 1,
        foto_eliminata like '%' AND foto_profilo like '%'
        )

Upvotes: 1

RandomSeed
RandomSeed

Reputation: 29759

Yes, you can use IF() wherever a value is expected, even though most of the time it is not the best route to take.

WHERE IF(path IS NULL, portfolio_id = 15, TRUE)

But prefer:

WHERE path IS NULL OR portfolio_id = 15

(notice you cannot compare with NULL, [anything] = NULL is always FALSE)

Upvotes: 0

Konerak
Konerak

Reputation: 39763

You can just use binary logic for this:

SELECT * FROM TABLE WHERE A
(if b then also where C)

Becomes

SELECT * FROM TABLE WHERE A 
AND (!b OR C)

In your case:

SELECT  
IF(path IS NOT NULL, concat("/uploads/attachments/",path, "/thumbnails/" , nome), "/uploads/attachments/default/thumbnails/avatar.png") as avatar_mittente
FROM prof_foto  
WHERE profilo_id = 15 
AND (path = "/uploads/attachments/default/thumbnails/avatar.png" OR foto_eliminata = 0 AND foto_profilo = 1))

Upvotes: 0

Related Questions