Hello, I need search in doTable on columns “month” and “title” the follow values:
For column “month”: Jan, Feb, Mar and Apr;
For column “title”: Latium, Abruzzo, Molise, Tuscany, Umbria, Campania and Sardinia.
I tried this query but in the output I don’t have one row where it is present in column title the string “Tuscany and Umbria”.
Can you help me?
Thank you in advance.
SELECT *
FROM
`doTable`
WHERE
month LIKE '%Jan%'
OR '%Feb%'
OR '%Mar%'
OR '%Apr%'
AND title LIKE '%Latium%'
OR '%Abruzzo%'
OR '%Molise%'
OR '%Tuscany%'
OR '%Umbria%'
OR '%Campania%'
OR '%Sardinia%';
If you don’t have anything in your columns other than “Jan”, “Feb”, “Mar”, then there is no need for the wild cards.
SELECT * FROM `doTable`
WHERE
-- This takes the left 3 of the month column and matches them exactly. Removing the need for the wildcard
LEFT(month,3) IN ('Feb', 'Mar', 'Apr')
-- These must match exactly.
AND title IN ('Abruzzo', 'Molise', 'Tuscany', 'Umbria', 'Campania', 'Sardinia')
thank you for help.
I tried this but the output is empty:
SELECT *
FROM
`doTable`
WHERE
month LIKE ('%Jan%'
OR '%Feb%'
OR '%Mar%'
OR '%Apr%')
AND title LIKE ('%Latium%'
OR '%Abruzzo%'
OR '%Molise%'
OR '%Tuscany%'
OR '%Umbria%'
OR '%Campania%'
OR '%Sardinia%');
Oh, well there really is no simple shortcut for that.
SELECT * FROM `doTable`
WHERE
LEFT(month,3) IN ('Feb', 'Mar', 'Apr')
AND (title LIKE '%Umbria%'
OR title LIKE '%Tuscany%'
OR title LIKE '%Abruzzo%'
........... and so on and so forth
) -- end parenthesis
There are some other things you can do, like storing these in a temp table or a real table and searching through it that way… but I think it’s beyond the scope of what you’re trying to accomplish.
mysql> SELECT
*
FROM
`doTable`
WHERE
`MONTH` IN ('Jan', 'Feb', 'Mar', 'Apr')
AND (
title LIKE '%Umbria%'
OR title LIKE '%Tuscany%'
OR title LIKE '%Abruzzo%'
OR title LIKE '%Latium%'
OR title LIKE '%Molise%'
OR title LIKE '%Campania%'
OR title LIKE '%Sardinia%'
);
+-----------------------------------------------------------------------------------------------------------------------------------+-------+----+
| title | month | id |
+-----------------------------------------------------------------------------------------------------------------------------------+-------+----+
| Umbria and Tuscany are two of Italy's most beautiful regions. | Jan | 1 |
| Abruzzo, Latium and Molise National Park is famous in Italy, | Jan | 2 |
| Sardinia is the second largest island in the Mediterranean Sea (after Sicily and before Cyprus) and an autonomous region of Italy | Mar | 5 |
+-----------------------------------------------------------------------------------------------------------------------------------+-------+----+
3 rows in set
mysql>