Query Help

[squidster]

Hi, I have a table that records contact details as follows:-

CREATE TABLE contact (
contactId int(10) unsigned NOT NULL auto_increment,
sourceId int(7) unsigned default NULL,
companyId int(10) unsigned default NULL,
title varchar(10) NOT NULL default '',
firstname varchar(20) NOT NULL default '',
surname varchar(20) NOT NULL default '',
tel1 varchar(18) default NULL,
tel2 varchar(18) default NULL,
email varchar(30) default NULL,
fax varchar(18) default NULL,
url varchar(75) default NULL,
type varchar(10) NOT NULL default '',
PRIMARY KEY (contactId),
) TYPE=InnoDB;

Am having trouble trying to query so it will return the contacts name and the contacts source name (the sourceId identifies another contact record as the source for a given contact).

Any ideas please.

thanks

 

[swampBoogie]

select c.firstName, c.surName,
s.firstName as sourceFirstName,
s.surName as sourceSurName
from conctact c left join contact s
on c.sourceId = s.contactId

If a contact does not have a source the query will return null for sourceFirstName and sourceSurName

 

[squidster]

Thanks very much thats excellent!