[Durham] SQL LEFT JOIN help...
Richard at helpquick.co.uk
Sun Nov 21 21:06:40 UTC 2010
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
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 ;-)
Mobile: 07921 512 459
The headquarters of
innovative IT solutions
Office: 0191 2582888, Fax: 0191 6408666
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
More information about the Durham