Hey not like that, i mean just change that "WHERE" statement, remaining query remains same Look here :
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 HAVING c.last_name LIKE '%smith%' ORDER BY c.first_names, c.last_name; On Wed, May 28, 2008 at 4:19 PM, Charlie Markwick <[EMAIL PROTECTED]> wrote: > 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 <php_mysql%40yahoogroups.com> [mailto: > php_mysql@yahoogroups.com <php_mysql%40yahoogroups.com>] On > Behalf Of aslam doctor > Sent: 28 May 2008 11:01 > To: php_mysql@yahoogroups.com <php_mysql%40yahoogroups.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]<cmdm%40southcot.com> > > > 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]
