macromedia.dreamweaver.appdev
[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" <makowiec@xxxxxxxxxxxxxxx> wrote in message news:Xns9953D155879D9makowiecatnycapdotrE@xxxxxxxxxxxxxxxxx
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
http://makowiec.net/
Email: http://makowiec.net/contact.php


Joe,

It sounds like you need a nested repeat region, have a look at Tom Muck's extension http://www.tom-muck.com/extensions/help/simulatednestedregion/ you could order by Member and then repeat the Categories

--
Regards,

Dave Buchholz
I-CRE8
www.i-cre8.co.uk
SkypeID: I-CRE8


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