joining 1 table's "set" field to another table field

Eep__

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.

the_Igel

I never worked with sets, but maybe this will work:

WHERE genres.genreID in compare.genreID
or
WHERE genres.genreID in (compare.genreID)

Eep__

SELECT
genres.genre
FROM
compare,genres
WHERE
genres.genreID
in
(compare.genreID)

also gives "role-playing" instead of "action,adventure". :/

Perhaps I'm going about storing the genreIDs as a set incorrectly though anyway? I don't know how else to efficiently store multiple genres without having to add a compareID column to the genres table. :\

the_Igel

SELECT genres.genre FROM compare,genres
WHERE find_in_set(genres.genreID, compare.genreID) > 0

Maybe not comletely what you need, but it's a good start.
Also have a glance at this page:
http://www.mysql.com/doc/en/SET.html

Eep__

OK, that sorta worked...got me the correct genre names at least, though it duplicated the entries instead of sticking "action" and "adventure" together into the genres.genre column.

How do you know what functions are possible with WHERE? The MySQL manual is unbelievably lacking in MANY basic explanations and examples--and it's VERY poorly categorized. There's no find_in_set() function listed on 6.3 Functions for Use in SELECT and WHERE Clauses: http://www.mysql.com/doc/en/Functions.html. Uh...

Anyway, thanks for your help; I'll keep dorking around with it--emphasis on DORKING, as MySQL most definitely was created by the hardcorest of dorks...<grumble>

the_Igel

I think no need to blame 'em that hard.
In my opinion, someone who's going to use sets should read what's written on them in manual. 🙂
But of course, mySQL manual isn't the most complete and clear manual in the world.

Eep__

But I HAVE read about sets in the manual--they're just not explained and exampled very well at all.
The manual just needs a LOT more self-referential links--especially since they don't know how to separate things very well with space, multiple pages, etc.

For example, anything relating to "set" should be linked off every page that mentions "set". So there should be a link to FIND_IN_SET().

6.3 Functions for Use in SELECT and WHERE Clauses makes it seem like the only function possible is MOD()--but it doesn't even show an example with a WHERE in it! Lame...

I KNOW the benefits of referential links because I use them extensively throughout my RenderWare object creation site: http://tnlc.com/rw/

MySQL's documentation authors need to learn how to write technical documentation better so it's easier to follow and understand.

the_Igel

Right you are 🙂

mattr

You shouldn't use SET or ENUM in MySQL. It is nonstandard, and as you have seen, poorly documented! 🙂

Eep__

Then how do I do it? Do I have the genres table with fields like this:

genreID
genre

or:

genreID
action
adventure
role-playing
simulation
strategy

How can I make compare.genreID reflect multiple values in the genres table without having to add a compareID field to the genres table? I'm trying to be as relational as possible without duplicating fields.

mattr

CREATE TABLE compare ( 
  genreID   TINYINT NOT NULL, 
  compareID TINYINT NOT NULL,
PRIMARY KEY( genreID, compareID )
) 


CREATE TABLE genres ( 
  genreID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  genre   VARCHAR( 20 ) NOT NULL, 
)

Eep__

And how to store multiple genres in compare.genreID? See, I don't want to have to store compare.id in the genres table. How do I do it? That's why I was using the "set" type for compare.genreID so I can store multiple genreIDs in it without having to store compare.ID in the genres table.

And what's the query to get all genres per game?

Oh and only one field can have primary key!

[deleted]

...enter the cross-reference table.

One table with X, one table with Y, and one table that holds the links between X and Y. Every link between one record in X and one record in Y is represented by one record in the cross-reference table.

Right now you're using the SET as a cross-refence table, and that's bad database design because of all the problems you just had. A cross-reference table is completely flexible and very easy to maintain.

the_Igel

If easiness isn't the main problem, and efficiency is the issue, you may leave alone cross-reference table and the set and use bigint instead, since set is anyway considered as bitwise bigint.

i.e. set of genres is defined by bits set on in a field. You can get genre names as follows:

select genre.name from genres, games
WHERE (games.genres >> (genres.genreID - 1))

But it's not THAT easy and, besides, you're limited to 64 genres (basically; still it's easy to work around).

Eep__

But the compare table is supposed to be the cross-reference (relationship) table. The way you're saying is I would have to have ANOTHER table with compareID and genreID, which is what I DON'T want to have to do because it will duplicate data unnecessarily.

See, I have many other tables linking to "compare" which is the main cross-reference table:

And I don't want to have to create more cross-reference tables that will duplicate data that could more efficiently be stored in a single cross-reference table, "compare". That's my dilemna with relational database design...

Eep__

Originally posted by the_Igel
If easiness isn't the main problem, and efficiency is the issue, you may leave alone cross-reference table and the set and use bigint instead, since set is anyway considered as bitwise bigint.

i.e. set of genres is defined by bits set on in a field. You can get genre names as follows:

select genre.name from genres, games
WHERE (games.genres >> (genres.genreID - 1))

But it's not THAT easy and, besides, you're limited to 64 genres (basically; still it's easy to work around).

OK! Now we're getting somewhere! 🙂 64 genres is PLENTY, and I can use this "bitwise bigint" method for the other lookup tables too!

Do you have any more detailed info (website) that explains this method? Thanks.

[deleted]

"OK! Now we're getting somewhere! 64 genres is PLENTY, and I can use this "bitwise bigint" method for the other lookup tables too! "

Like Igel says, it's not THAT easy.

For ex, the genres must have a bitwise id, 0,1,2,4,8,16,32 etc So you'll have to create those manually, you can't use a sequence or auto_increment for that.

Don't get too bent on saving a few bytes of hd space, you might end up writing way too much code.

Eep__

Well, it's not about saving disk space so much as it is about being efficient. Why duplicate data that doesn't have to be duplicated? I don't see the logic in putting compareID into the genres table, and I don't want to have multiple listings for different genres in the compare table.

Besides, surely there's a way to auto-increment bitwise fields too! If not in MySQL then through PHP...

[deleted]

Ofcourse you don't put the compareid into the genres table, you create a new table that holds the compareid and the genreid so it refers to the compare-table on one side, and and the genre-table on the other.

Like I said earlier, a cross-reference table between the compare table and the genres table.

Eep__

But like I said earlier, I don't want to have all these cross-reference tables (just look at the relations.jpg I provided a link to above to see how many I would need--ugh!) since it will mean unnecessarily duplicating data that could be better managed more efficiently.

Remember, the compare table doesn't just have genreID in it but at least 9 other ID fields. It is, in effect, THE single cross-reference table of the entire database--or at least I want it to be.

I'm attempting to go for full 5DNF here (I think). I still need to research this further, obviously...