Based on a poor database design beyond my control, I am now having to deal with something new (for me). I am hoping someone can assist me.
We have two columns in the database: "Column1" and "Column2" Column1 can have a value of Breakfast/Lunch/Dinner. Column2 is free text and can contain many things.
How can I write a query where I can look for more than one specific value in Column1 and Column2 where both conditions must be true?
Select * from TestDb where (Column1 = 'Breakfast' and Column2 like '%banana%') and (Column1 = 'Lunch' and Column2 like '%pizza%')
The expected result is that we find all subjects who had a banana for breakfast and pizza for lunch (which is why both conditions must be true). We don't want to find subject who just had a banana for breakfast or just pizza for lunch.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `TestDb` ADD INDEX `testdb_idx_column1` (`Column1`);
SELECT * FROM TestDb WHERE ( TestDb.Column1 = 'Breakfast' AND TestDb.Column2 LIKE '%banana%' ) AND ( TestDb.Column1 = 'Lunch' AND TestDb.Column2 LIKE '%pizza%' )