Hi, just change this "WHERE c.last_name LIKE '%smith%'" to "HAVING c.last_name LIKE '%smith%'"
I think it will work, reply me back. On Wed, May 28, 2008 at 3:09 PM, Charlie Markwick <[EMAIL PROTECTED]> wrote: > 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 > > -- A.M.Doctor [Non-text portions of this message have been removed]
