Not working I will send you a sample text file with the table structure and some sample data.
Charlie -----Original Message----- From: php_mysql@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of aslam doctor Sent: 28 May 2008 12:12 To: php_mysql@yahoogroups.com Subject: Re: [php_mysql] Problem using joins 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: 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:58 > To: php_mysql@yahoogroups.com <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: php_mysql@yahoogroups.com > > <php_mysql%40yahoogroups.com><php_mysql% > 40yahoogroups.com> [mailto: > > php_mysql@yahoogroups.com <php_mysql%40yahoogroups.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> > > <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] ------------------------------------ The php_mysql group is dedicated to learn more about the PHP/MySQL web database possibilities through group learning. Yahoo! Groups Links ---------- Table: contacts contact_id,company_id,division_id,address_id,home_address_id,salutation,last_name,first_names,gender,date_of_birth,summary,title,description,email,email_status,work_phone,work_phone_ext,cell_phone,home_phone,fax,aol_name,yahoo_name,msn_name,interests,profile,custom1,custom2,custom3,custom4,entered_at,entered_by,last_modified_at,last_modified_by,contact_record_status,, 79,38,63,57,1,Mr.,Smith,Sam,m,,,Team Manager,,[EMAIL PROTECTED],a,01 12346789,0,,,,,,,,,,,,,01/02/2007 22:39,9,14/03/2007 13:51,3,a,, 181,76,230,1,1,Mrs.,Smith (UMIS),Jean,f,,,,,[EMAIL PROTECTED],a,01 12346789,0,,,,,,,,,,,,,14/03/2007 14:10,2,18/03/2007 16:29,3,a,, 183,78,233,1,1,Ms.,De Smith,Julie,f,,,,,[EMAIL PROTECTED],a,01 12346789,0,,,,,,,,,,,,,14/03/2007 14:10,2,18/03/2007 16:25,3,a,, 337,216,0,191,1,Ms.,Smith,Mary,f,,,,,[EMAIL PROTECTED],a,01 12346789,0,,,,,,,,,,,,,14/03/2007 14:33,3,14/03/2007 14:34,3,a,, Table: companies company_id,user_id,company_source_id,industry_id,crm_status_id,rating_id,account_status_id,company_name,company_code,legal_name,tax_id,profile,phone,phone2,fax,url,employees,revenue,credit_limit,terms,entered_at,entered_by,last_modified_at,last_modified_by,default_primary_address,default_billing_address,default_shipping_address,default_payment_address,custom1,custom2,custom3,custom4,extref1,extref2,extref3,company_record_status 38,9,1,31,4,1,1,Business 1,Pcode 1,Legal 1,,,01 2314697,,,,,,0,0,01/02/2007 22:39,9,02/08/2007 12:29,5,57,57,57,57,,,,,,,,a 76,2,2,31,1,1,1,Business 2,Pcode 2,Legal 2,,,2 2314697,,,,,,0,0,14/03/2007 14:10,2,09/12/2007 09:45,7,105,105,105,105,,,,,,,,a 78,2,2,31,1,1,1,Business 3,Pcode 3,Legal 3,,,3 2314697,,,,,,0,0,14/03/2007 14:10,2,02/08/2007 11:57,5,107,107,107,107,,,,,,,,a 216,3,2,30,1,1,1,Business 4,Pcode 4,Legal 4,,,4 2314697,,,,,,0,0,14/03/2007 14:33,3,14/03/2007 17:03,2,293,191,191,191,,,,,,,,a Table: company_division division_id,company_id,address_id,user_id,company_source_id,industry_id,division_name,description,entered_at,entered_by,last_modified_at,last_modified_by,custom1,custom2,custom3,custom4,division_record_status,,,,,,,,,,,,,,,,,,, 63,38,0,0,0,0,Divison 1,,14/03/2007 13:49,3,14/03/2007 13:49,3,,,,,a,,,,,,,,,,,,,,, 230,76,0,0,0,0,Divison 2,,18/03/2007 15:56,3,18/03/2007 15:56,3,,,,,a,,,,,,,,,,,,,,, 233,78,0,0,0,0,Divison 3,,18/03/2007 16:24,3,18/03/2007 16:24,3,,,,,a,,,,,,,,,,,,,,, Table: addresses address_id,company_id,country_id,address_name,address_body,line1,line2,city,province,postal_code,address_type,use_pretty_address,offset,daylight_savings_id,address_record_status,,,,,,,,,,,,,,,,, 1,1,216,Address 1,,123 high street,,somewheretown,a county,SO11 111,unknown,f,.,,a,,,,,,,,,,,,,,,,, 57,38,216,Address 2,,line1 2,line2 2,line3 2,province 2,postc 2,unknown,f,,,a,,,,,,,,,,,,,,,,, 191,216,216,Address 3,,line1 3,line2 3,line3 3,province 3,postc 3,unknown,f,,,a,,,,,,,,,,,,,,,,, /* SQLyog Enterprise - MySQL GUI v6.12 MySQL - 4.0.24_Debian-10ubuntu2.1-log : Database - TempDB ********************************************************************* */ /*!40101 SET NAMES latin1 */; /*!40101 SET SQL_MODE=''*/; create database if not exists `TempDB`; USE `TempDB`; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*Table structure for table `addresses` */ DROP TABLE IF EXISTS `addresses`; CREATE TABLE `addresses` ( `address_id` int(11) NOT NULL auto_increment, `company_id` int(11) NOT NULL default '0', `country_id` int(11) NOT NULL default '1', `address_name` varchar(100) NOT NULL default '', `address_body` varchar(255) NOT NULL default '', `line1` varchar(255) NOT NULL default '', `line2` varchar(255) NOT NULL default '', `city` varchar(255) NOT NULL default '', `province` varchar(255) NOT NULL default '', `postal_code` varchar(255) NOT NULL default '', `address_type` varchar(20) NOT NULL default 'unknown', `use_pretty_address` char(1) NOT NULL default 'f', `offset` float default NULL, `daylight_savings_id` int(10) unsigned default NULL, `address_record_status` char(1) NOT NULL default 'a', PRIMARY KEY (`address_id`), KEY `company_id` (`company_id`), KEY `city` (`city`), KEY `province` (`province`), KEY `address_record_status` (`address_record_status`) ) TYPE=MyISAM; /*Table structure for table `companies` */ DROP TABLE IF EXISTS `companies`; CREATE TABLE `companies` ( `company_id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `company_source_id` int(11) NOT NULL default '0', `industry_id` int(11) NOT NULL default '0', `crm_status_id` int(11) NOT NULL default '0', `rating_id` int(11) NOT NULL default '0', `account_status_id` int(11) NOT NULL default '0', `company_name` varchar(100) NOT NULL default '', `company_code` varchar(10) NOT NULL default '', `legal_name` varchar(100) NOT NULL default '', `tax_id` varchar(100) NOT NULL default '', `profile` text NOT NULL, `phone` varchar(50) NOT NULL default '', `phone2` varchar(50) NOT NULL default '', `fax` varchar(50) NOT NULL default '', `url` varchar(50) NOT NULL default '', `employees` varchar(50) NOT NULL default '', `revenue` varchar(50) NOT NULL default '', `credit_limit` int(11) NOT NULL default '0', `terms` int(11) NOT NULL default '0', `entered_at` datetime default NULL, `entered_by` int(11) NOT NULL default '0', `last_modified_at` datetime default NULL, `last_modified_by` int(11) NOT NULL default '0', `default_primary_address` int(11) NOT NULL default '0', `default_billing_address` int(11) NOT NULL default '0', `default_shipping_address` int(11) NOT NULL default '0', `default_payment_address` int(11) NOT NULL default '0', `custom1` varchar(100) NOT NULL default '', `custom2` varchar(100) NOT NULL default '', `custom3` varchar(100) NOT NULL default '', `custom4` varchar(100) NOT NULL default '', `extref1` varchar(50) NOT NULL default '', `extref2` varchar(50) NOT NULL default '', `extref3` varchar(50) NOT NULL default '', `company_record_status` char(1) default 'a', PRIMARY KEY (`company_id`), KEY `company_record_status` (`company_record_status`) ) TYPE=MyISAM; /*Table structure for table `company_division` */ DROP TABLE IF EXISTS `company_division`; CREATE TABLE `company_division` ( `division_id` int(11) NOT NULL auto_increment, `company_id` int(11) NOT NULL default '0', `address_id` int(11) default NULL, `user_id` int(11) NOT NULL default '0', `company_source_id` int(11) NOT NULL default '0', `industry_id` int(11) NOT NULL default '0', `division_name` varchar(100) NOT NULL default '', `description` text NOT NULL, `entered_at` datetime default NULL, `entered_by` int(11) NOT NULL default '0', `last_modified_at` datetime default NULL, `last_modified_by` int(11) NOT NULL default '0', `custom1` varchar(100) NOT NULL default '', `custom2` varchar(100) NOT NULL default '', `custom3` varchar(100) NOT NULL default '', `custom4` varchar(100) NOT NULL default '', `division_record_status` char(1) default 'a', PRIMARY KEY (`division_id`) ) TYPE=MyISAM; /*Table structure for table `contacts` */ DROP TABLE IF EXISTS `contacts`; CREATE TABLE `contacts` ( `contact_id` int(11) NOT NULL auto_increment, `company_id` int(11) NOT NULL default '0', `division_id` int(11) NOT NULL default '0', `address_id` int(11) NOT NULL default '1', `home_address_id` int(11) NOT NULL default '1', `salutation` varchar(20) NOT NULL default '', `last_name` varchar(100) NOT NULL default '', `first_names` varchar(100) NOT NULL default '', `gender` char(1) NOT NULL default 'u', `date_of_birth` varchar(100) NOT NULL default '', `summary` varchar(100) NOT NULL default '', `title` varchar(100) NOT NULL default '', `description` varchar(100) NOT NULL default '', `email` varchar(100) NOT NULL default '', `email_status` char(1) default 'a', `work_phone` varchar(50) NOT NULL default '', `work_phone_ext` int(11) NOT NULL default '0', `cell_phone` varchar(50) NOT NULL default '', `home_phone` varchar(50) NOT NULL default '', `fax` varchar(50) NOT NULL default '', `aol_name` varchar(50) NOT NULL default '', `yahoo_name` varchar(50) NOT NULL default '', `msn_name` varchar(50) NOT NULL default '', `interests` varchar(50) NOT NULL default '', `profile` text NOT NULL, `custom1` varchar(50) NOT NULL default '', `custom2` varchar(50) NOT NULL default '', `custom3` varchar(50) NOT NULL default '', `custom4` varchar(50) NOT NULL default '', `entered_at` datetime default NULL, `entered_by` int(11) NOT NULL default '0', `last_modified_at` datetime default NULL, `last_modified_by` int(11) NOT NULL default '0', `contact_record_status` char(1) NOT NULL default 'a', PRIMARY KEY (`contact_id`), KEY `company_id` (`company_id`), KEY `contact_record_status` (`contact_record_status`) ) TYPE=MyISAM; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; [Non-text portions of this message have been removed]
