SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Search in table on two columns

    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.
    Code:
    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%';

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use parentheses around your OR conditions

    alternatively, use IN lists
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot bronze trophy mawburn's Avatar
    Join Date
    Apr 2014
    Posts
    197
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    If you don't have anything in your columns other than "Jan", "Feb", "Mar", then there is no need for the wild cards.

    Code:
    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')

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use parentheses around your OR conditions

    alternatively, use IN lists
    thank you for help.
    I tried this but the output is empty:
    Code:
    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%');

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mawburn View Post
    If you don't have anything in your columns other than "Jan", "Feb", "Mar", then there is no need for the wild cards.

    Code:
    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, the output is empty:
    Code:
    SELECT * FROM `doTable` 
    WHERE  
        LEFT(month,3) IN ('Feb', 'Mar', 'Apr')
        AND title IN ('Abruzzo', 'Molise', 'Tuscany', 'Umbria', 'Campania', 'Sardinia')

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Example of strings in column title:
    Umbria and Tuscany are two of Italy's most beautiful regions.
    Abruzzo, Latium and Molise National Park is famous in Italy,

  7. #7
    SitePoint Zealot bronze trophy mawburn's Avatar
    Join Date
    Apr 2014
    Posts
    197
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Example of strings in column title:
    Oh, well there really is no simple shortcut for that.

    Code:
    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.

  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thank you, now working:
    Code:
    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>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    may you never forget parentheses again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •