Thanks for the speedy response, it doesn't seem to make any difference I'm afraid.
select * from contacts having last_name LIKE '%smith%'; Returns 40 results whilst my select using like or having produces 302 results. Charlie -----Original Message----- From: php_mysql@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of aslam doctor Sent: 28 May 2008 11:01 To: php_mysql@yahoogroups.com Subject: Re: [php_mysql] Problem using joins 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
