Excellent, thanks for that. I have used the first way as the second way gave
me different results (lower record count) from what I was getting with it
via a server side script. The first way provide me with the same record
count. Thanks.
Best regards
>>> Andrew Dixon
-----Original Message-----
From: Dobromir Velev [mailto:[EMAIL PROTECTED]
Sent: 11 June 2004 12:58
To: Andrew Dixon - MSO.net; [EMAIL PROTECTED]
Subject: Re: Query Help
Hi,
You could use either something like this
SELECT gallery_id, gallery_name
FROM galleries g
WHERE keywords rlike '(^|,)$keyword_id(,|$)';
or
SELECT gallery_id, gallery_name
FROM galleries g
WHERE $keyword_id in (keywords);
and replace the $keyword_id with the id of the keyword you are looking for.
Personally I would prefer the first option and put an index on the keywords
field but you should check for yourself which query will work faster.
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Friday 11 June 2004 14:22, 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:
>
> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
