How to JOIN multiple tables and display multiple rows from each table?

[G Marshall]

Hi,

I am trying to do a complex SELECT / JOIN and have become very confused.

Here is my situation (I have simplied it some)

I have 3 Tables: Members, Addresses, Contacts

A Member can have 0 to many Addresses (home, work, summer, etc)
A Membercan also have 0 to many Contacts (phone, email, mobile, etc)

CREATE TABLE `Members` (
`MemberID` int(10) unsigned NOT NULL auto_increment,
`FirstName` varchar(40) default NULL,
`LastName` varchar(40) default NULL,
P RIMARY KEY (`MemberID`)
) TYPE=MyISAM;

CREATE TABLE `Addresses` (
`AddressID` int(10) unsigned NOT NULL auto_increment,
`Members_MemberID` int(10) unsigned NOT NULL default '0',
`AddressType` varchar(15) default NULL,
`Address` varchar(40) default NULL,
`City` varchar(40) default NULL,
`State` varchar(40) default NULL,
PRIMARY KEY (`AddressID`),
) TYPE=MyISAM;

CREATE TABLE `Contacts` (
`ContactID` int(10) unsigned NOT NULL auto_increment,
`Members_MemberID` int(10) unsigned NOT NULL default '0',
`ContactType` varchar(15) default NULL,
`ContactData` varchar(40) default NULL,
PRIMARY KEY (`ContactID`),
) TYPE=MyISAM;

I am trying to create a SELECT / JOIN statement so that I display a list of a Member's First and Last name with all of their addresses and contacts. I am using the following SELECT statement:

SELECT *
From Members M
LEFT JOIN Addresses A ON A.Members_MemberID = M.MemberID
LEFT JOIN Contacts C ON C.Members_MemberID = M.MemberID

When I do this, I get a lot of rows (more than I should).

I want to display something like this (where members may have 0 to many addresses, 0 to many contact options):

Name................ Address.....................................Contact

Jim Brown.........home: 1 main st, town1, UT............cell: 123-9999
........................work: 10 red st, town2, FL...............home: 888-9000
.............................................................................work: 887-9022
.............................................................................email: [e-mail]

Tom Black.........work: 1 toon st, town1, UT..............cell: 123-9999
........................summer: 55 bad rd, town2, MS..........home: 888-9000

Sam White.........work: 5 hot ave, town5, CA............email: [e-mail]
.............................................................................home: 888-9000

Mike Blue...............................................................email: [e-mail]

The SELECT statment above produces too many rows.

Another way I thought to do it is to SELECT all the Members first, then loop through each of the Members and SELECT from the Addresses table, then the Contrats Table. That seems like a very ineffcient way to do it.

Can someone help me understand this? I have mainly developed with just one or two tables in the past so this is new for me.

How does everyone else do this? Thanks



Edited 1 time(s). Last edit at 07/30/2007 02:42PM by G Marshall.