I have a group of tables and I am trying to display the details of a
contact including their address, company address and the divisional
address. I have listed the relevant part of the tables below. I am
trying to use joins and I am very much a newbie at them. The selects I
am creating do pass back data but they also produce duplicates and the
duplicates do not necessarily have the correct address data. Can some
kind person help me out of my misery?
The select:-
SELECT
c.salutation, c.first_names, c.last_name,
c.title, c.description, c.work_phone, c.email,
addHome.address_name, addHome.address_body, addHome.line1,
addHome.line2, addHome.city, addHome.postal_code,
ltd.company_name,
addBus.address_name, addBus.address_body, addBus.line1,
addBus.line2, addBus.city, addBus.postal_code,
div.division_name,
addDiv.address_name, addDiv.address_body, addDiv.line1,
addDiv.line2, addDiv.city, addDiv.postal_code,
c.contact_record_status
FROM
contacts c
LEFT JOIN companies ltd ON ltd.company_id=c.company_id
LEFT JOIN company_division div ON div.division_id=c.division_id
LEFT JOIN addresses addHome ON addHome.address_id=c.address_id
LEFT JOIN addresses addBus ON c.company_id=addBus.company_id
LEFT JOIN addresses addDiv ON c.company_id=addDiv.company_id
WHERE
c.last_name LIKE '%smith%'
ORDER BY
c.first_names, c.last_name;
Charlie
Table - contacts which have amongst other the fields:
contact_id
company_id
division_id
address_id
home_address_id
salutation
last_name
first_names
gender
date_of_birth
Email
contact_record_status
Table - addresses which have amongst other the fields:
address_id
company_id
country_id
address_name
address_body
line1
line2
city
province
postal_code
Table- companies which have amongst other the fields:
company_id
user_id
company_name
Table - company_division which have amongst other the fields:
division_id
company_id
address_id
