[Top] [All Lists]

Re: Many <-> Many in MySQL

Subject: Re: Many <-> Many in MySQL
From: "Dave Buchholz"
Date: Tue, 19 Jun 2007 09:36:47 +0100
Newsgroups: macromedia.dreamweaver.appdev

"Joe Makowiec" <[email protected]> wrote in message news:[email protected]
I have a classic many-to-many situation: a members table and a category
table, joined by an intermediate membersCategory table.

CREATE TABLE `members` (
 `memberNum` int(10) unsigned NOT NULL auto_increment,
 `member` varchar(90) collate ascii_bin NOT NULL default '',
 `address` varchar(70) collate ascii_bin NOT NULL default '',
-- etc
 PRIMARY KEY  (`memberNum`)
)  ;

CREATE TABLE `categories` (
 `Category` varchar(50) NOT NULL default '',
 `categoryID` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`categoryID`)
) ;

CREATE TABLE `memberCat` (
 `memberNum` bigint(20) NOT NULL default '0',
 `categoryID` int(11) NOT NULL default '0',
 PRIMARY KEY  (`memberNum`,`categoryID`),
 KEY `categoryID` (`categoryID`)
) ;

I can extract all categories for a given member; I can extract all
members for a given category.  What I'd like to do is something like:

member, address, category1, category2, ...

Where I get back a list of members, with each member record containing
a list of all categories that the member belongs to.  I'm guessing that
I need some kind of subquery for this, but haven't been able to find
out how to do it yet.

Joe Makowiec


It sounds like you need a nested repeat region, have a look at Tom Muck's extension you could order by Member and then repeat the Categories


Dave Buchholz
SkypeID: I-CRE8

<Prev in Thread] Current Thread [Next in Thread>