MySQL conditional query

Question

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?

Answer 1

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

Answer 2

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

Answer 3

Try this:

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

Answer 4

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

Answer 5

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)