List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 25 1999 4:34pm
Subject:Re: Problems with join
View as plain text  
martok@stripped wrote:
> 
> Hi,
> 
> I am hoping someone here can help me out with a join select problem I am
> having.  I am relatively new to joins so am not surprised this doesn't
> work.  I designed a little phonebook database but cannot get the data out
> of it properly.
> 
> create table name(
> name_id smallint not null auto_increment,
> name varchar(32) not null,
> primary key(name_id));
> 
> create table number(
> number_id smallint not null auto_increment,
> number varchar(80) not null,
> type enum('primary','alternate','cel','fax') default 'primary' not null,
> primary key(number_id));
> 
> create table name_and_number(
> name_id smallint not null,
> number_id smallint not null,
> primary key(name_id,number_id));
> 
> So with that setup, a person can have more than one number associated with
> the name but how do I get the data out in order?  E.G.
> 
> insert into name values(1,'John Smith');
> insert into number values(1,'555-1212');
> insert into number values(2,'555-1313');
> insert into name_and_number values(1,1);
> insert into name_and_number values(1,2);
> 
> So John Smith has a primary phone and a cel, no alternate or fax but how
> do you select it so that it returns say the name in row[0], primary in
> row[1], alternate in row[2] row[2] == NULL if no alternate, row[3] = cel
> and row[4] = fax?
> 
> I understand that left join will set the columns to null if there is no
> match but how do you order them as shown above?

Hi Martok

You have to combine LEFT JOIN and self JOIN here:
SELECT
	name.name
	, IFNULL(n1.number, '--none--') AS primary
	, IFNULL(n2.number, '--none--') AS alternate
	, IFNULL(n3.number, '--none--') AS cel
	, IFNULL(n4.number, '--none--') AS fax
FROM
	name
	LEFT JOIN name_and_number AS nan1 ON (name.name_id = nan1.name_id)
	LEFT JOIN number AS n1 ON (nan1.number_id = n1.number_id)
	LEFT JOIN name_and_number AS nan2 ON (name.name_id = nan2.name_id)
	LEFT JOIN number AS n2 ON (nan2.number_id = n2.number_id)
	LEFT JOIN name_and_number AS nan3 ON (name.name_id = nan3.name_id)
	LEFT JOIN number AS n3 ON (nan3.number_id = n3.number_id)
	LEFT JOIN name_and_number AS nan4 ON (name.name_id = nan4.name_id)
	LEFT JOIN number AS n4 ON (nan4.number_id = n4.number_id)

Note:
This query is getting slow for medium tablesizes. therefore it would be better, to get the
numbers for each person in multiple rows with:
SELECT
	name.name
	,number.type
	,number.number
FROM
	name
	,name_and_number AS nan
	,number
WHERE
	name.name_id = nan.name_id
	AND nan.number_id = number.number_id


For this simple application I would also suggest, to add a column (name_id) to the
'number' table instead of creating a third table 'name_and_number'.
This third table should only be used, if there are telephone numbers equal for different
persons too.

Tschau
Christian

Thread
Problems with joinmartok23 May
  • Re: Problems with joinChristian Mack25 May