[TOASTDamocles]

Hi All,

I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:

CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;


CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;


CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;

The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.

What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!!

Heres my current query:

SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid

Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?

Thanks in advance to anyone who can help

Damocles.

[andrewst]

SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid

[r937]

select business_contact.Title
     , business_contact.Surname 
     , business_contact.FirstName 
     , company.Name
     , company.Manager
  from business_contact
left outer
  join works_for
    on business_contact.BusContactID  
     = works_for.BusContactID 
left outer
  join company
    on works_for.CompanyID 
     = company.CompanyID

[TOASTDamocles]

Thanks to both for your replies, works great!

I could really do to read a decent SQL tutorial at some point!

Damocles.