CODEWITHSUNDEEP
mysql
psyb0rg
psyb0rg

Reputation: 85

MySQL conditional query

I have a situation where I've got file names stored in two fields in a table. The fields are called file1 an file2.

file1 is ALWAYS present. file2 might not be present for some rows.

I want to write a query that would get me only a certain extension of files from the DB.

For that I am using for example 

...WHERE file1 LIKE '%jpg%'

But here I cannot use AND file2 LIKE '%jpg%' because file2 may be empty, which is ok.

So what do I do so that the LIKE condition is only applied when the field is not empty?

Upvotes: 0

Views: 390

Answers (5)

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

SELECT  * 
FROM    files 
WHERE   file1 LIKE '%jpg' AND 
        (file2 LIKE '%jpg' OR file2 IS NULL);

Test case:

CREATE TABLE files (file1 varchar(20), file2 varchar(20));

INSERT INTO files VALUES ('pic1.jpg', NULL);
INSERT INTO files VALUES ('pic2.png', NULL);
INSERT INTO files VALUES ('pic3.jpg', 'otherpic.jpg');
INSERT INTO files VALUES ('pic4.png', 'nopic.jpg');
INSERT INTO files VALUES ('pic5.bmp', 'otherpic.gif');

Returns:

+----------+--------------+
| file1    | file2        |
+----------+--------------+
| pic1.jpg | NULL         |
| pic3.jpg | otherpic.jpg |
+----------+--------------+
2 rows in set (0.00 sec)

Upvotes: 2

AllenG
AllenG

Reputation: 8190

Try AND (file2 is NULL or file2 LIKE %jpg%)

Upvotes: 2

Noah Goodrich
Noah Goodrich

Reputation: 25263

Try this:

AND (file2 LIKE '%jpg%' OR file2 IS NULL (or file2 = '' depending on what your empty value is) )

Upvotes: 2

sleepynate
sleepynate

Reputation: 8036

Make use of parenthesis to clarify.

WHERE file1 LIKE '%jpg%' AND (file2 LIKE '%jpg%' OR file2 is NULL OR file2 = '')

or whatever other conditions you need from file2

Upvotes: 2

Jeremy DeGroot
Jeremy DeGroot

Reputation: 4506

Could use just use OR file2 LIKE '%jpg%'?

Upvotes: 0

Related Questions