Help wth double left join

[squidster]

Hi,

I'm having a bit of trouble with a query which requires I guess what you call a double left join?

the query is:-

SELECT p.placementId, p.contactId, p.SBUId, p.placementStart, p.placementEnd, p.workDesc, p.placementNotes, c.conTitle, c.conFirstname, c.conSurname, s.conTitle AS studentTitle, s.conFirstname AS studentFirstname, s.conSurname AS studentSurname
FROM placement p
LEFT JOIN contact c ON p.contactId = c.contactId
LEFT JOIN contact s ON p.SBUId = c.SBUId
WHERE p.placementId = 1

which displays almost the correct result but studentTitle, studentFirstname, studentSurname all show NULL.

the tables concerned are:-

CREATE TABLE placement (
  placementId int(10) unsigned NOT NULL auto_increment,
  contactId int(10) unsigned NOT NULL default '0',
  SBUId int(7) default '0',
  placementStart date NOT NULL default '0000-00-00',
  placementEnd date default NULL,
  workDesc text,
  placementNotes text,
  PRIMARY KEY  (placementId)
) TYPE=MyISAM;


CREATE TABLE contact (
  contactId int(10) unsigned NOT NULL auto_increment,
  SBUId int(7) unsigned default NULL,
  conSourceId int(7) unsigned default NULL,
  companyId int(10) unsigned default NULL,
  roleId int(2) unsigned default NULL,
  conTitle varchar(10) NOT NULL default '',
  conFirstname varchar(20) NOT NULL default '',
  conSurname varchar(20) NOT NULL default '',
  conTel1 varchar(18) default NULL,
  conTel2 varchar(18) default NULL,
  conEmail varchar(30) default NULL,
  conFax varchar(18) default NULL,
  conUrl varchar(75) default NULL,
  conType varchar(12) NOT NULL default '',
  roomNo varchar(5) default NULL,
  placementYear year(4) default NULL,
  PRIMARY KEY  (contactId),
  UNIQUE KEY SBUId (SBUId)
) TYPE=MyISAM;

Can someone tell me where I'm going wrong?

thanks

 

[squidster]

Doh!!

Spotted my mistake, should have been:-

LEFT JOIN contact s ON p.SBUId = s.SBUId

You just never can check enough!!

Still someone might find post useful