At 12:22 +0100 on 06/11/2004, Andrew Dixon - MSO.net wrote about Query Help:
Hi Everyone.
I have the following a table with a varchar column that contains a comma delimited list of id's from another table that relates the item keywords in the other table.
The table keywords contains
keyword_id (int/auto increment/primary key) Keyword (varchar/normal key)
The galleries table contains:
gallery_id (int/auto increment/primary key) gallery_name (varchar) keywords (varchar)
I didn't design the database and I know it is not a good design, but I'm stuck with it and I need a query to get the gallery_id when I have a certain keyword_id
For example:
gallery_id | gallery_name | keywords 1 | test | 1,2,3,4 2 | test2 | 3,4,5,6
And I won't to get all the galleries with where the have the keywords 2, which in this case would be record 1 or keyword 4 which would be both record.
SELECT gallery_id, gallery_name FROM galleries WHERE keywords ????
Hope that makes sense, thanks in advanced.
Best Regards,
Andrew Dixon.
I have two suggestions for your problem.
You can remove the Keywords Column and create a table with gallery_id and keyword as its content (IOW: A table with 1/1, 1/2, 1/3, 1/4, 2/3, 2/4, 2/5, 2/6 rows) with the Primary key as Keyword, gallery_id [so it can use the index to read only the correct group).
As an alternative, you can alter the keywords from Varchar (or Char) to SET. To do this, just do a dump of the table, alter the keywords definition in the CREATE TABLE to SET(1,2,3,4,5,6) and then feed the commands back in. It will parse the "1,2,3...." in the INSERT into the correct bits.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
