SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Simple LIKE query not working

    Hello,

    I have a field in database in which i have stored comma seperated keywords like: abc, def, ghi, jkl

    Then I am doing a query like: "SELECT * FROM tablename WHERE keywords_field LIKE %def%";

    I am doing a simple query like that but its not returning any result. How to do ? I have used * also instead of % but that is also giving any results. Is it due to the comma seperated keywords in database ?

    How to fix ?

    Thanks.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just checking the obvious are you putting %def% in quotes. Without them it is an invalid sql syntax.

    Code SQL:
    SELECT * FROM tablename WHERE keywords_field LIKE '%def%'

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    I have a field in database in which i have stored comma seperated keywords like: abc, def, ghi, jkl
    i urge you to redesign it

    Quote Originally Posted by Tapan View Post
    I am doing a simple query like that but its not returning any result.
    that's correct, it isn't returning any result because it's returning an error message instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i urge you to redesign it

    that's correct, it isn't returning any result because it's returning an error message instead

    Hi,

    Why redesign it ? And yes you are correct it was returning an error.

    Thanks.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    *plays swami with Rudy*

    Because if multiple entries have the same keywords, it's less redundant to store them in a separate table and join the two by id's...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    Why redesign it ?
    because it violates first normal form

    fix it, and your queries will be (1) simpler, and (2) faster

    are those reasons sufficient?

    Quote Originally Posted by StarLion View Post
    *plays swami with Rudy*
    sorry, swami be wrong

    i would use the actual keywords, ~not~ ids

    besides, the "redundancy" is not eliminated by using ids, that's a huge myth -- there would be just as many redundant id values as keyword values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    We've had this conversation before - unless the keywords were shorter than 3 characters, id's are faster :P

    comma seperated values do not necessarily violate 1NF, but should still be avoided. My google-fu is strong.

  8. #8
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    because it violates first normal form

    fix it, and your queries will be (1) simpler, and (2) faster

    are those reasons sufficient?

    sorry, swami be wrong

    i would use the actual keywords, ~not~ ids

    besides, the "redundancy" is not eliminated by using ids, that's a huge myth -- there would be just as many redundant id values as keyword values
    Hi,

    Makes sense.

    Thanks.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    We've had this conversation before
    yes, and you lost the argument the last time, too

    using ids requires a join

    i don't care how fast your ids are when doing joins, nothing beats not doing the join at all



    Quote Originally Posted by StarLion View Post
    comma seperated values do not necessarily violate 1NF, but should still be avoided.
    yes, they do, and yes they should

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, and you lost the argument the last time, too

    using ids requires a join

    i don't care how fast your ids are when doing joins, nothing beats not doing the join at all

    So many-to-many... you're suggesting he put... what? You dont want him to do a join, you dont want him to put CSV.... what was the actual suggestion? I'm lost.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    what was the actual suggestion? I'm lost.
    "i'll explain it so even you can understand it" -- moe howard

    (nothing personal intended, i just thought it was an apt quote)


    products
    prodcode
    descr
    price
    PK ( prodcode )

    productkeywords
    prodcode FK
    keyword [FK]*
    PK ( prodcode , keyword )

    will that suffice or would you like to see sample data?

    this design ~is~ actually many-to-many, by the way

    * the keywords table may or may not exist, depending on whether you want a controlled vocabulary or freeform
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Oh okay, so it IS joining. You're just saying avoid the join table. gotcha.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    productkeywords is the join table, the table between products and keywords (if the keywords table exists)

    it's a separate table in order to avoid the comma-separated list in the products table

    naturally, you ~do~ have to join back to the products table to find the product data

    but you ~don't~ have to join to the keywords table, as would be required by the keyword ids you were suggesting in post #5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    If the Keywords table exists (which would be the case if you wanted to limit keyword choices... otherwise the keywords table is redundant and irrelevant), you have to join to the keywords table....

    How do you not join to it?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    If the Keywords table exists (which would be the case if you wanted to limit keyword choices... otherwise the keywords table is redundant and irrelevant)...
    so far, so good

    Quote Originally Posted by StarLion View Post
    ... you have to join to the keywords table....

    How do you not join to it?
    you don't join to it by not joining to it

    i'm not sure what you aren't getting

    list products along with their keywords for selected products --
    Code:
    SELECT p.prodcode
         , p.descr
         , p.price
         , GROUP_CONCAT(pk.keywords) AS keywords
      FROM product AS p
    LEFT OUTER
      JOIN productkeywords AS pk
        ON pk.prodcode = p.prodcode
     WHERE p.descr LIKE '%pants%'
    GROUP
        BY p.prodcode
    find products which have a certain keyword --
    Code:
    SELECT p.prodcode
         , p.descr
         , p.price
      FROM productkeywords AS pk
    INNER
      JOIN product AS p 
        ON p.prodcode = pk.prodcode
     WHERE pk.keyword = 'sandal'
    see? no keywords table is required in the queries, even though it exists for referential integrity of keywords

    methinks you've been using ids too long
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    an interesting point... though you've inflated your database by storing "sandal" over and over and over instead of 1 over and over...

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    you've inflated your database by storing "sandal" over and over and over instead of 1 over and over...
    so? your point is?

    let me ask you this, when you create a persons table, do you extract all the first names and store them in a separate table, replacing them in the persons table with an id, just to avoid storing "john" and "mary" over and over instead of 42 and 63 over and over?

    didn't think so
    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
  •