Re: MySQL and "set" data type table joining

From: Eepē <eep_at_tnlcNOSPAM.com>
Date: Tue, 01 Oct 2002 19:26:58 GMT
Message-ID: <3D99F712.4B655D6B_at_tnlcNOSPAM.com>


Anyone?

Eepē wrote:

> 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 Tue Oct 01 2002 - 21:26:58 CEST

Original text of this message