Yes sorry I wasn't clear enough. I did this and changing "WHERE" for "HAVING" makes no difference in either case it produces 302 results whereas there should only be 40.
Charlie -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of aslam doctor Sent: 28 May 2008 11:58 To: [email protected] Subject: Re: [php_mysql] Problem using joins 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: [email protected] <php_mysql%40yahoogroups.com> [mailto: > [email protected] <php_mysql%40yahoogroups.com>] On Behalf Of > aslam doctor > Sent: 28 May 2008 11:01 > To: [email protected] <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] ------------------------------------ The php_mysql group is dedicated to learn more about the PHP/MySQL web database possibilities through group learning. Yahoo! Groups Links
