Robert A. Rosenberg wrote:
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).
Right. That's the best way, though it would probably be a good idea to create the relation table first, then drop the keywords column. That way, you can use the existing data to populate the new table.
CREATE TABLE gallery_keywords SELECT g.gallery_id, k.keyword_id FROM galleries g, keywords k WHERE FIND_IN_SET(k.keyword_id, g.keywords);
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.
You can simply alter the table to change the column to a SET type and the data will be converted in place, though dumping the data first as a backup, as you suggest, is wise. It is unclear to me, however, how this would be an improvement over the current scheme. Furthermore, as a SET column is limited to 64 elements, you would be limited to 64 keywords this way.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
