Question
Many <-> Many in MySQL
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
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
AdChoices