Re: MySQL and "set" data type table joining

Is this the best way to store "lookup" values in a separate table with a "set" datatype in the main field so multiple genres can be selected without having to add a compare.id column to the genres table?

Eep² wrote:

> I'm trying to join a "set" field from one table to another table with the set's contents in it.
>
> table setup:
>
> CREATE TABLE compare (
> id tinyint(4) NOT NULL auto_increment,
> genreID set('1','2','3','4','5','6') default NULL,
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> INSERT INTO compare VALUES (1,'1,2');
> INSERT INTO compare VALUES (2,'1,2');
> INSERT INTO compare VALUES (3,'1,2');
> INSERT INTO compare VALUES (4,'1,2');
> INSERT INTO compare VALUES (5,'1,2');
>
> CREATE TABLE genres (
> genreID tinyint(1) NOT NULL auto_increment,
> genre varchar(20) default NULL,
> PRIMARY KEY (genreID)
> ) TYPE=MyISAM;
>
> INSERT INTO genres VALUES (1, 'action');
> INSERT INTO genres VALUES (2, 'adventure');
> INSERT INTO genres VALUES (3, 'role-playing');
> INSERT INTO genres VALUES (4, 'simulation');
> INSERT INTO genres VALUES (5, 'strategy');
> INSERT INTO genres VALUES (6, 'other');
>
> query:
>
> SELECT
> genres.genre
> FROM
> compare,genres
> WHERE
> compare.genreID=genres.genreID
>
> Now, what I want to happen is when compare.genreID=1,2 the genres.genre should come out "action,adventure" (or, more preferably, "action-adventure"). Right now it comes out as "role-playing" (or "3" using genres.genreID) for some odd reason, as if the set is just being added together instead of remaining a set.
>
> Help! Thanks.
Received on Mon Sep 30 2002 - 08:44:15 CEST