Simple LIKE query not working

Databases
1

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

Just checking the obvious are you putting %def% in quotes. Without them it is an invalid sql syntax.

SELECT * FROM tablename WHERE keywords_field LIKE '%def%'
3

i urge you to redesign it :slight_smile:

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

:slight_smile:

4

Hi,

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

Thanks.

5

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

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 :slight_smile:

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

7

We’ve had this conversation before - unless the keywords were shorter than 3 characters, id’s are faster :stuck_out_tongue:

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

8

Hi,

Makes sense.

Thanks.

9

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

:stuck_out_tongue:

yes, they do, and yes they should

:slight_smile:

10

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

“i’ll explain it so even you can understand it” – moe howard

:slight_smile: (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? :slight_smile:

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
12

Oh okay, so it IS joining. You’re just saying avoid the join table. gotcha.

13

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

14

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

so far, so good :slight_smile:

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 –

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 –


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 :wink:

16

an interesting point… though you’ve inflated your database by storing “sandal” over and over and over instead of 1 over and over…

17

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 :smiley: