IMHO, I create link tables which have the many-to-many relationships as you are looking for. Here's a small example
CREATE TABLE users ( iUserID INT(4) NOT NULL AUTO_INCREMENT, cUsername VARCHAR(15) NOT NULL UNIQUE, PRIMARY KEY(iUserID)); CREATE TABLE games ( iGameID INT(4) NOT NULL AUTO_INCREMENT, cGameName VARCHAR(25), PRIMARY KEY (iGameID)); CREATE TABLE usersGamesLink ( iLinkID INT(8) NOT NULL AUTO_INCREMENT, iUserID INT(4) NOT NULL, iGameID INT(4) NOT NULL, PRIMARY KEY (iLinkID)); -- Now, for the SELECT statements, we can left join any games that belong to an associated user. For example, say your username is 'ryan' SELECT games.cGameName FROM games LEFT JOIN usersGamesLink ON (usersGamesLink.iGameID=games.iGameID) LEFT JOIN users ON (users.iUserID=usersGamesLink.iUserID) WHERE users.cUsername='ryan'; There are more ways to adjust that query based on your intentions, so that is not the most optimized query you can have. I'm just using it as an example.... This will give you your many to many relationship without needing to load all of the keys into memory and try to sort through them. to come up with many other select statements for each one. Hopefully, this helps a bit, at least for giving you the right idea. After you read this, I would recommend reading 'how mysql optimizes left join and right join' (http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html) and 'EXPLAIN syntax (get information about a SELECT)' (http://www.mysql.com/doc/en/EXPLAIN.html) Thanks, Ryan Yagatich On Sun, 2003-06-15 at 21:02, Lingua2001 wrote: > Hi, > > while trying to handle severl key values, > I wonder if it is more efficient to put > those values in a row seperated by a > certain seperator (eg. comma). > > The talbe has two fields and looks like; > > UID ForeinID > 1 2,3,4,6 > 2 7,9,4,5 > 3 3,4 > 4 1,5,7,9 > 5 2,3,4,5 > > and the values of ForeignID is primary keys > of another table. > > What would be the best way to handle this kind of > case (i.e. M-to-M relationship) in MySQL? > > Thanks in advance. > > Lingua -- ,_____________________________________________________, \ Ryan Yagatich [EMAIL PROTECTED] \ / Pantek Incorporated (877) LINUX-FIX / \ http://www.pantek.com/security (440) 519-1802 \ / Are your networks secure? Are you certain? / \___A9062F5C3EAE81D54A28A8C1289943D9EE43015BD8BC03F1___\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
