Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unhappy Unanswered: Relatively easy SQL SELECT statement issues :/

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2004
    Posts
    2

    Thanks!

    Thanks to both for your replies, works great!

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

    Damocles.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •