so is it working or not??? if not working then plz mail me your tables with some data, i will solve it as soon as possible.
On Wed, May 28, 2008 at 4:26 PM, Charlie Markwick <[EMAIL PROTECTED]> wrote: > 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] <php_mysql%40yahoogroups.com> [mailto: > [email protected] <php_mysql%40yahoogroups.com>] On > Behalf Of aslam doctor > Sent: 28 May 2008 11:58 > To: [email protected] <php_mysql%40yahoogroups.com> > 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]<cmdm%40southcot.com> > > > 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><php_mysql% > 40yahoogroups.com> [mailto: > > [email protected] <php_mysql%40yahoogroups.com> <php_mysql% > 40yahoogroups.com>] On Behalf Of > > aslam doctor > > Sent: 28 May 2008 11:01 > > To: [email protected] <php_mysql%40yahoogroups.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><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 > > > -- A.M.Doctor [Non-text portions of this message have been removed]
