Hi Folks

Just a newbie looking for some help using MySQL verions 4.016. Here is a DB design with three tables: Members (members address_book), Clubs (list of clubs,addresses,etc) and Positions (list of positions). The relationships between the three tables are as follows:

Club may have many Members...
Members may belong to many Clubs...
Members may have Many positions with different Clubs
Clubs may have many Positions from many Members

-------------------------
CREATE TABLE members (
memberid int(11) NOT NULL auto_increment,
fname varchar(50) default NULL,
lname varchar(50) default NULL,
....other fields ....
PRIMARY KEY (id)
) TYPE=MyISAM;

--------------------------------
CREATE TABLE clubs (
clubno int(12) NOT NULL default '0',
name varchar(30) NOT NULL default '',
contact varchar(30) NOT NULL default '',
....other fields .....
PRIMARY KEY (regno)
) TYPE=MyISAM;

--------------------------------

CREATE TABLE positions (
posid int(2) NOT NULL auto_increment,
positiontitle varchar (50) not null,
PRIMARY KEY (ID)
);

sample data for positions
INSERT INTO position VALUES ('1', 'Chairperson');
INSERT INTO position VALUES ('2', 'Vice Chair');
INSERT INTO position VALUES ('3', 'Past Chair');
INSERT INTO position VALUES ('4', 'Secretary');
INSERT INTO position VALUES ('5', 'Treasurer');
INSERT INTO position VALUES ('6', 'Executive Director');
etc....

----------------------------------

Do I need to create a fourth table to relate my three main tables.

CREATE TABLE relation (
memberid int(11) NOT NULL,
clubno int(12) NOT NULL,
posid int(2) default NULL,
) TYPE=MyISAM;

I need to be able to select all the executive members for each club with their titles...

Any help would be appreciated?

Danny

yes, you need that 4th table

by the way your PRIMARY KEY(xxx) clauses are all wrong, they appear to reference columns (e,g, ID) that you haven't declared in the table -- probably just typos, right?

rudy
http://r937.com/

Yes... they were just typo's

CREATE TABLE members (
memberid int(11) NOT NULL auto_increment,
....other fields ....
PRIMARY KEY (memberid)
) TYPE=MyISAM;

--------------------------------
CREATE TABLE clubs (
clubno int(12) NOT NULL default '0',
....other fields .....
PRIMARY KEY (clubno)
) TYPE=MyISAM;

--------------------------------

CREATE TABLE positions (
posid int(2) NOT NULL auto_increment,
positiontitle varchar (50) not null,
PRIMARY KEY (posid)
);

I'm now having abit of problems with my Select statment to ouptut a list of all clubs, with members name & positions(title)... Any help on that would be appreciated.

Thanks
Danny

what's your query look like so far?

This is what I have and it works...

Danny

that looks fine, except you will probably get more mileage out of using JOIN syntax instead of the table list syntax

JOIN syntax allows you to isolate the join conditions to the two tables they pertain toso, what problems were you having with it?

Hi

My select statement worked fine - I just thought there could be a better way of doing it! What is the benefit of using a join versus the way I did it.

Thanks

Danny

what benefit? JOIN syntax allows you to isolate the join conditions to the two tables they pertain to

in addition, once you are comfortable with JOIN syntax, it is a trivial matter to code outer joins, whereas with the table list syntax, it's a p.i.t.a. if not outright impossible

rudy