SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Non-Member bronze trophy geniusgoalie's Avatar
    Join Date
    Sep 2004
    Location
    Buffalo, USA
    Posts
    979
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    searching a database for multiple matches

    Is there any way to check for multiple variable matches in mysql? For example, you would have variables $A up to $I, and you would first display rows with all nine variables matching their respective columns, down to displaying last the rows with only one matching variable?
    I can't figure out a better way to do it than individual "if" statements for each possibility- which would be 72
    If anyone has a better idea, I would really appreciate it if you would enlighten me
    Thanks
    Dan

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select columnA
         , columnB
         , columnC
         , columnD
         , columnE
         , columnF
         , columnG
         , columnH
         , columnI
         , case when columnA = '$A' then 1 else 0 end
          +case when columnB = '$B' then 1 else 0 end
          +case when columnC = '$C' then 1 else 0 end
          +case when columnD = '$D' then 1 else 0 end
          +case when columnE = '$E' then 1 else 0 end
          +case when columnF = '$F' then 1 else 0 end
          +case when columnG = '$G' then 1 else 0 end
          +case when columnH = '$H' then 1 else 0 end
          +case when columnI = '$I' then 1 else 0 end
             as relevancy
      from yourtable
     where columnA = '$A' 
        or columnB = '$B' 
        or columnC = '$C' 
        or columnD = '$D' 
        or columnE = '$E' 
        or columnF = '$F' 
        or columnG = '$G' 
        or columnH = '$H' 
        or columnI = '$I'      
    order
        by relevancy desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member bronze trophy geniusgoalie's Avatar
    Join Date
    Sep 2004
    Location
    Buffalo, USA
    Posts
    979
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never would have thought of doing it that way, thanks a lot!


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
  •