Re: Query Help

Michael Stassen Mon, 14 Jun 2004 20:48:45 -0700

First, you really should add a gallery_keywords relationship table to replace the galleries.keywords column. Aside from the difficulties you are already having, I don't think any solution with the current scheme will be able to make full use of indexes, if at all. In other words, without an indexed relationship table, these queries will all be slow.

That said, so long as there are no spaces in galleries.keywords, FIND_IN_SET() will do what you want until you can fix the design.

Find galleries with keyword 2:

  SELECT gallery_id, gallery_name
  FROM galleries
  WHERE FIND_IN_SET(2,keywords);

Find galleries with keyword 'some_keyword':

  SELECT g.gallery_id, g.gallery_name
  FROM galleries g, keywords k
  WHERE FIND_IN_SET(k.keyword_id,g.keywords)
  AND k.keyword='some_keyword';

Find keywords for gallery 'test':

  SELECT k.keyword
  FROM keywords k, galleries g
  WHERE FIND_IN_SET(k.keyword_id,g.keywords)
  AND g.gallery_name='test';

Michael

Andrew Dixon - MSO.net wrote:

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:

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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Help

Reply via email to