Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Where with Multiple Or

    I need to write a wheres statement with multiple Or conditions. What I have is below and is currently working, but not sure if it is proper syntax or if it is working 'now' and will not pull accurately later on. I need to say
    Code:
    Select * from db1 
    Where (itemSold1 Like '%Je%' OR itemSold1 Like '%Me%') 
    Or (itemSold2 Like '%Je%' OR itemSold2 Like '%Me%') 
    Or (itemSold3 Like '%Je%' OR itemSold3 Like '%Me%') 
    Or (itemSold4 Like '%Je%' OR itemSold4 Like '%Me%') 
    Or (itemSold5 Like '%Je%' OR itemSold15 Like '%Me%')
    Last edited by jo15765; 09-17-14 at 11:39. Reason: better formatted the code

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The syntax is correct. The only problem you will face is if you need to put an AND in the logic somewhere. Then you will need to put parentheses around all of your ORs to force the order of operations to consider them as a block.

    As an aside, you can simplify the logic a little:
    Code:
    create table #test
    (col1 varchar(10))
    
    insert into #test values ('jelly'), ('melon'), ('juice'), ('neither')
    
    select *
    From #test
    where col1 like '[jm]e%'

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why do you have columns called itemSold1, itemSold2, itemSold3, itemSold4 and itemSold5?

    Sounds like a design mistake to me
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    he could be selling finger cozies.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by gvee View Post
    Why do you have columns called itemSold1, itemSold2, itemSold3, itemSold4 and itemSold5?

    Sounds like a design mistake to me
    Design mistake of epic proportions! The retailer wanted to keep each item sold in an individual field in the database, so that is what they got.

    Hence the querying multiple fields with the same condition.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moral: don't let the customer dictate the technical design. Would have been far better to store it properly and then display it how they want it when desired.
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its the designers job to translate the customer requirement into an effective design. to me it sounds like you are blaming the customer for not doing the design process correctly in the first place (or you took over someone else's design)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by healdem View Post
    its the designers job to translate the customer requirement into an effective design. to me it sounds like you are blaming the customer for not doing the design process correctly in the first place (or you took over someone else's design)
    Not blaming a customer in any way, was just stating why the initial design was set-up the way that it was.

    Quote Originally Posted by healdem View Post
    (or you took over someone else's design)
    Hit the nail on the head with that one my friend!

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What did the nail ever do to him, then?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could reduce the pain a little bit by using:
    Code:
    Select *
       FROM db1
       WHERE itemSold1 + itemSold2 + itemSold3 + itemSold4 + itemSold5 LIKE '%[JM]e%'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, since the like is looking for things to start with J or M, and followed by E, you would want to rewrite Pat's query a little (which is a shock to me).
    Code:
    Select *
       FROM db1
       WHERE  '::' + itemSold1 + '::' + itemSold2 +  '::' + itemSold3 +  '::' + itemSold4 +  '::' + itemSold5 LIKE '%::[JM]e%'
    Here I am making the blatant assumption that the string '::' does not appear in any of the items sold. If it does, you would want to come up with a string that does not exist in the itemsoldn columns.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I thought about using a "guard character" like the :: in MCrowley's example just in case one item ended with a 'J' or 'M' and the following item started with an 'e'. For this purpose, the guard character can be any character except for 'e' and does NOT need to be included in the wildcard.

    As I'm not convinced that this code isn't a contrived example, I didn't bother with the guard character... Without knowing exactly what SQL will be executed there isn't a way for me to guess what guard character is safe.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, I am wrong. The original post was looking for strings that only contain, not start with 'je' or 'me'.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    I tried to re-construct intermediate/temporary table (somemore normalized).
    Code:
    SELECT r.*
     FROM  db1 AS r
     CROSS JOIN
           (VALUES
               (itemSold1) , (itemSold2) , (itemSold3)
             , (itemSold4) , (itemSold5) , (itemSold6)
             , (itemSold7) , (itemSold8) , (itemSold9)
            /* so on ... */
           ) AS q(itemSold)
     WHERE q.itemSold Like '%Je%'
      OR   q.itemSold Like '%Me%'
    ;

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Or you can put both options together and make it one less line of SQL.

    Code:
    SELECT r.*
     FROM  db1 AS r
     CROSS JOIN
           (VALUES
               (itemSold1) , (itemSold2) , (itemSold3)
             , (itemSold4) , (itemSold5) , (itemSold6)
             , (itemSold7) , (itemSold8) , (itemSold9)
            /* so on ... */
           ) AS q(itemSold)
     WHERE q.itemSold Like '%[JM]e%'
    ;
    Dave

Posting Permissions

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