How to get rows by filtering multiple columns which contain values in MySQL?

Question

I have a MySQL table which is named fruits:

I want to select rows from the table where the columns fruit1, fruit2, and fruit3 only contain values in the set [apple, pear, melon] i.e.

1. only have apple
2. only have pear
3. only have melon
4. only have both apple and pear
5. only have both apple and melon
6. only have both pear and melon
7. only have apple, pear and melon

For my sample data, the rows will be 1, 5, 8.

I have tried this query:

SELECT * FROM fruits WHERE 'apple' in (fruit1, fruit2, fruit3)

which gave me all the rows that contain apple in one of the fruit columns. However I'm not sure how to filter on multiple columns.

Answer 1

Ideal Solution:

You can normalize it by creating two tables instead. First table would be storing id and area.

Create Table area_master (area_id int auto_increment, 
                          area varchar(32), 
                          description varchar(32)
                          Primary Key(area_id));
Insert into area 
       (area, description) 
Values ('areaA', 'Hi'), 
       ('areaB', 'Hello'); -- add more rows as you need

Second table would be storing area_id (FK from area_master table), attribute, value, attribute_order:

Create Table area_fruits (id int auto_increment, 
                          area_id int, 
                          attribute varchar(32), 
                          value varchar(32), 
                          attribute_order int
                          Primary Key(id));

Insert into area_fruits 
       (area_id, attribute, value, attribute_order) 
Values (1, 'fruit', 'apple', 1),
       (2, 'fruit', 'banana', 1),
       (2, 'fruit', 'apple', 3); -- add more rows as needed

Now, the query to fetch the data would simply be the following, and you will not need to change it every time, as you add new fruits :-)

SELECT am.id, am.area, am.description 
FROM area_master AS am 
JOIN area_fruits AS af ON af.area_id = am.area_id AND 
                          af.attribute = 'fruit' 
GROUP BY am.id, am.area, am.description 
HAVING SUM(af.value IN ('apple', 'pear', 'melon')) = COUNT(*) 

P.S: Lot more refinements is possible, such as adding Foreign Key constraints, etc. But I hope you get the gist.

---

Original Problem:

• Using Where we consider only those rows where atleast one the fruit columns have either apple, pear, or melon
• Now, we do a Group By on the id, and use Having to ignore the rows where fruit columns are not empty, and their value is something else other than
  apple, pear, and melon.

Try the following:

SELECT id 
FROM fruits 
WHERE fruit1 IN ('apple', 'pear', 'melon') OR 
      fruit2 IN ('apple', 'pear', 'melon') OR 
      fruit3 IN ('apple', 'pear', 'melon')
GROUP BY id 
HAVING SUM(fruit1 NOT IN ('apple', 'pear', 'melon') AND fruit1 <> '') = 0 AND 
       SUM(fruit2 NOT IN ('apple', 'pear', 'melon') AND fruit2 <> '') = 0 AND 
       SUM(fruit3 NOT IN ('apple', 'pear', 'melon') AND fruit3 <> '') = 0

P.S This is a bad design. Whenever you have n number of similar columns, you should consider normalizing it.

Answer 2

I've worked on the assumption that blank fruit values in your table are NULL. In that case this query will give you the desired result:

SELECT *
FROM fruits
WHERE (fruit1 IN ('apple', 'melon', 'pear') OR fruit1 IS NULL)
  AND (fruit2 IN ('apple', 'melon', 'pear') OR fruit2 IS NULL)
  AND (fruit3 IN ('apple', 'melon', 'pear') OR fruit3 IS NULL)

Output

id  fruit1  fruit2  fruit3
1   apple       
5   melon           pear
8   apple           melon

If the blank values are just empty strings, change the query to

SELECT *
FROM fruits
WHERE (fruit1 IN ('apple', 'melon', 'pear') OR fruit1 = '')
  AND (fruit2 IN ('apple', 'melon', 'pear') OR fruit2 = '')
  AND (fruit3 IN ('apple', 'melon', 'pear') OR fruit3 = '')

Demo on dbfiddle