Multiple Join Query question 1

[ttmac]

Hi all,

Thanks in advance for any help provided.

MySQL version: 4.0.20a-nt.

I have created normalised tables for individuals. I have created a seperate table to hold all work and home email addesses for individuals. There will also be cases where the individual will not have an email address or will have one or the other.

I am trying to compose a query that will return all data for individuals on a single row.

The following is the SQL statement I have created thus far: -

SELECT distinct
	t.FirstName,  
	t.LastName,  
	--tbl.address_line_1,  
	--tbl.address_line_2,  
	--tbl.address_line_3,  
	--tbl.address_line_4,  
	--tbl.address_line_5,
	emw.Email_Address as work_email,
	emh.Email_Address as home_email
FROM 
	tbl_Members t 
		left join tbl_Members_has_tbl_Addresses tb 
			on (tb.tbl_Members_idtbl_Member=t.idtbl_Member )
		left join tbl_Addresses tbl 
			on (tb.tbl_Addresses_idtbl_Addresses=tbl.idtbl_Addresses)  
		left  join tbl_Members_has_tbl_Email_Address e 
			on (e.tbl_Members_idtbl_Member=t.idtbl_Member )
		left outer join tbl_Email_Add emw 
			on (e.tbl_Email_Add_idtbl_Email_Add=emw.idtbl_Email_Add and
				upper(emw.Home_Work)="W")  
		left  outer join tbl_Email_Add emh 
			on (e.tbl_Email_Add_idtbl_Email_Add=emh.idtbl_Email_Add and
				upper(emh.Home_Work)="H")

The results I am getting at the moment are :

Firstname 	Lastname 		Work_Email  			Home_Email
Tom			McE				Work_email@address		null
Tom			McE				null					Home_email@address

What I am trying to achieve is: -

Firstname 	Lastname 		Work_Email  			Home_Email
Tom			McE				Work_email@address		Home_email@address

Is this possible?

If so what do I need to do to achieve the desired results? (I believe that my problem is in my join statements)

Thanks for any help,

Tom.


Create statements for the relevant tables: -

CREATE TABLE tbl_Members (
  idtbl_Member INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  UserName VARCHAR(12) NULL,
  Passwrd VARCHAR(12) NULL,
  CFT_Number INTEGER UNSIGNED NOT NULL,
  FirstName VARCHAR(20) NOT NULL,
  LastName VARCHAR(20) NOT NULL,
  Date_Joined DATE NULL,
  PRIMARY KEY(idtbl_Member)
)
AUTO_INCREMENT = 1;

CREATE TABLE tbl_Members_has_tbl_Email_Address (
  tbl_Members_idtbl_Member INTEGER UNSIGNED NOT NULL,
  tbl_Email_Address_idtbl_Email_Address INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(tbl_Members_idtbl_Member, tbl_Email_Address_idtbl_Email_Address),
  INDEX tbl_Members_has_tbl_Email_Address_FKIndex1(tbl_Members_idtbl_Member),
  INDEX tbl_Members_has_tbl_Email_Address_FKIndex2(tbl_Email_Address_idtbl_Email_Address)
);


CREATE TABLE tbl_Email_Address (
  idtbl_Email_Address INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Home_Work VARCHAR(1) NULL,
  Email_Address VARCHAR(50) NULL,
  PRIMARY KEY(idtbl_Email_Address)
);

 

[TonyGroves]

If you have a maximum of one of each address type, the following little change
should work:
[tt]
SELECT
t.FirstName,
t.LastName,
MAX(emw.Email_Address) work_email,
MAX(emh.Email_Address) home_email
FROM
tbl_Members t
LEFT JOIN tbl_Members_has_tbl_Addresses tb
ON tb.tbl_Members_idtbl_Member=t.idtbl_Member
LEFT JOIN tbl_Addresses tbl
ON tb.tbl_Addresses_idtbl_Addresses=tbl.idtbl_Addresses
LEFT JOIN tbl_Members_has_tbl_Email_Address e
ON e.tbl_Members_idtbl_Member=t.idtbl_Member
LEFT JOIN tbl_Email_Add emw
ON
e.tbl_Email_Add_idtbl_Email_Add=emw.idtbl_Email_Add
AND UPPER(emw.Home_Work)="W"
LEFT JOIN tbl_Email_Add emh
ON
e.tbl_Email_Add_idtbl_Email_Add=emh.idtbl_Email_Add
AND UPPER(emh.Home_Work)="H"
GROUP BY firstname,lastname
[/tt]

 

[ttmac]

Thanks Tony,

A star for your help.

Is it possible to do this just using joins?

Tom.

 

[TonyGroves]

The trouble is the tbl_Members_has_tbl_Email_Address (phew!) table, which if I'm reading it correctly has an entry for each email address without specifying whether it's work or home, so you are always going to get 2 records for each member with 2 email addresses.

You might like to look at your database design, which seems to me to be overcomplicated. You could replace the aforementioned table plus the actual addresses table, with one table where each record contains the member id., address type, and address.

 

[ttmac]

Thanks Tony.

The long table names is posing a problem given that there seems to be a 32 character limitation on table/field names. This is a direct result of cutting corners and allowing DBDesigner to autogenerate lookup tables.

The initial design of the member/email address tables is a many to many relationship which will allow any member to use the same email address of another member. This is to cater for the scenario of family membership where the child might use the parents email address. The table tbl_Members_has_tbl_Email_Address is the lookup table between the two and was autogenerated.

Is there a better approach to solving the many-to-many relationship problem?

Tom.