[Durham] SQL LEFT JOIN help...

[Richard Patterson]

Hi Guys (and Gals),

I have an SQL query for you... (MySQL 5.1)

I have 4 tables, defined as such:

create table orders (id integer not null primary key auto_increment, order_ref varchar(10),
order_date date, order_value decimal(10,2), contact_id integer, address_id integer);

create table contacts (id integer not null primary key auto_increment,
contact_title varchar(4), contact_firstname varchar(20), contact_surname varchar(20),
company_id integer, address_id integer);

create table addresses (id integer not null primary key auto_increment,
address_dept varchar(60), address_1 varchar(60), address_2 varchar(60),
address_town varchar(60), address_city varchar(60), address_postcode varchar(10));

create table companies (id integer not null primary key auto_increment,
company_ref varchar(6), company_name varchar(60));


*NOTE* the orders table has no reference to the companies table (this can be obtained through the contacts table).
*Also* the contacts table has an "address_id" column, which is only used at the data entry point (a contact can have more than one address, and this points to their primary address)


OK, so now, I want to get information from all 4 tables in one select:

order_id, order_ref, order_date,  order_value, contact_title, contact_firstname,
contact_surname, company_ref, company_name, address_dpet, address_1, address_2,
address_town, address_city, address_postcode.


I can get the order information, plus contact information, plus company information using the following query (but no address info):

SELECT o.id, o.order_ref, con.contact_surname, com.company_name
FROM (
	orders o
	LEFT JOIN contacts con ON o.contact_id=con.id)
LEFT JOIN companies com ON con.company_id = com.id;


The above works as expected...

So, how can I add the address info in to the mix?

Also, I might need to add a "shipto_contact_id" and a "shipto_address_id" (both pointing to the same "contacts" and "addresses" tables), to the orders table...

How can I get the info for everything in one query?


Winner gets a couple of pints at the next meeting ;-)



Thanks

Richard


Richard Patterson
Mobile: 07921 512 459


HelpQuick Ltd
The headquarters of
innovative IT solutions

Office: 0191 2582888, Fax: 0191 6408666
Web: http://www.helpquick.co.uk

Have you used our services? Why not write a review on the FreeIndex website

HelpQuick Limited, Registered in England & Wales, Company number
5334746, Vat registration number: 859 6133 89, Registered office:
18 Camden Square, North Shields, NE30 1NR, UK