Multiple joins returning too many rows

**amitpatel66** · Feb 12 '08, 01:55 PM

Originally posted by nathj

Hi,

I've been puzzling out the problem below for several hours and it's driving me mad. I know there is a way to do what I want I just can't figure it out.

Here's the situation. I have the following tables:
tbl_Group, tbl_Coordinator , tbl_GroupCoordi nator, tbl_PAddress, tbl_EAddress, tbl_GroupPAddre ss tbl_GroupEAddre ss and tbl_Coordinator EAddress.

This allows for one group to have many coordinators and one coordinator to belong to many groups, a similar set with the P and E addresses.

Given a specific group ID I want to retrieve all group details and coordinator details from these tables. In my database at present I have one group. This group is correctly linked to three EAddresses and 1 PAddress. The group is linked to the only coordinator on the system and the coordinator in turn is linked to 3 EAddresses. The data itself is as I expect it to be. My trouble is the SQL;

Code:

SELECT 
a.ID as groupID, a.name as groupName, 
b.ID as coordinatorID, b.title, b.firstName, b.lastName, 
c.organisation, c.line1, c.line2, c.line3, c.line4, c.town, c.county, c.countryID, c.postcode, 
d.eAddress as gpTel, 
e.eAddress as gpMob,
f.eAddress as gpEmail,
g.eAddress as cdTel, 
h.eAddress as cdMob,
i.eAddress as cdEmail		
FROM tbl_Group a 
LEFT OUTER JOIN tbl_GroupCoordinator j ON j.groupID = a.ID 
LEFT OUTER JOIN tbl_Coordinator b ON b.ID = j.coordinatorID 
LEFT OUTER JOIN tbl_GroupPAddress k ON k.groupID = a.ID 
LEFT OUTER JOIN tbl_PAddress c ON c.ID = k.pAddressID  
LEFT JOIN tbl_GroupEAddress l ON l.groupID = a.ID
LEFT OUTER JOIN tbl_EAddress d ON d.ID = l.eAddressID AND d.eAddressType = 1
LEFT OUTER JOIN tbl_EAddress e ON e.ID = l.eAddressID AND e.eAddressType = 2
LEFT OUTER JOIN tbl_EAddress f ON f.ID = l.eAddressID AND f.eAddressType = 3
LEFT JOIN tbl_CoordinatorEAddress m ON m.coordinatorID = b.ID
LEFT OUTER JOIN tbl_EAddress g ON g.ID = m.eAddressID AND g.eAddressType = 1
LEFT OUTER JOIN tbl_EAddress h ON h.ID = m.eAddressID AND h.eAddressType = 2
LEFT OUTER JOIN tbl_EAddress i ON i.ID = m.eAddressID AND i.eAddressType = 3
WHERE a.ID = 1 ;

This results in 8 rows not 1. I get 8 rows showing each of the Group EAddresses along side one of the Coordinator EAddresses.

I know it's possible to do what I want, I've even managed it before but today for some reason I cannot figure it out, and can't remember where I did before to check the solution.

Any help on this is greatly appreciated.

Cheers
nathj

Since I donot have the table structure, I will not be able to test your query.
Please try removing all tables except the two and execute. then keep adding tables in to your query and check which is causing the problem of redundunt data?

**nathj** · Feb 12 '08, 03:23 PM

Originally posted by amitpatel66

Since I donot have the table structure, I will not be able to test your query.
Please try removing all tables except the two and execute. then keep adding tables in to your query and check which is causing the problem of redundunt data?

Thanks for stopping by. I did what you said and the problem happens as soon as I add a second instance of a tbale with a different alias.

My approach now is to use multiple queries and control the output from them in PHP.

Many thanks
nathj

**amitpatel66** · Feb 12 '08, 03:50 PM

Originally posted by nathj

Thanks for stopping by. I did what you said and the problem happens as soon as I add a second instance of a tbale with a different alias.

My approach now is to use multiple queries and control the output from them in PHP.

Many thanks
nathj

I see from your query that you have hardcoded emailtype value as 1,2,3, then you can look for using UNION clause for 2 queries one each for different emailtypes. Could you please try this?

**nathj** · Feb 12 '08, 04:02 PM

Originally posted by amitpatel66

I see from your query that you have hardcoded emailtype value as 1,2,3, then you can look for using UNION clause for 2 queries one each for different emailtypes. Could you please try this?

Hi,

I could use a union yes but the idea was that if I had one result set the different eAddress types need to represented as extra columns not extra rows - because of the way the results are processed afterwards in my PHP.

Because there is a certain amount of hard-coding going on I have managed to get enerything I need from 3 seprate queries.

thanks fro all your help and for pointing out some good practices for trackig stuff down.

Many thanks
nathj

**mwasif** · Feb 12 '08, 06:40 PM

It will be easy for us to debug if you had provided table structure and the output you are getting. Did you try GROUP BY?

**nathj** · Feb 13 '08, 11:50 AM

Originally posted by mwasif

It will be easy for us to debug if you had provided table structure and the output you are getting. Did you try GROUP BY?

I tried group by and that did not help either. I think the output I was getting, originally, is called a cartesian product but I'm not sure of that.

There are 8 tables involved and the structure is:

Code:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: db1187.oneandone.co.uk
-- Generation Time: Feb 13, 2008 at 12:49 PM
-- Server version: 5.0.45
-- PHP Version: 4.3.10-200.schlund.1
-- 
-- Database: `db222703999`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_Coordinator`
-- 

CREATE TABLE `tbl_Coordinator` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) collate latin1_general_ci NOT NULL,
  `firstName` varchar(255) collate latin1_general_ci NOT NULL,
  `lastName` varchar(255) collate latin1_general_ci NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `tbl_Coordinator`
-- 

INSERT INTO `tbl_Coordinator` VALUES (1, 'Mr', 'Duncan', 'Podbury', '2008-02-12', '2008-02-12');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_CoordinatorEAddress`
-- 

CREATE TABLE `tbl_CoordinatorEAddress` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `coordinatorID` int(10) unsigned NOT NULL,
  `eAddressID` int(10) unsigned NOT NULL,
  `isMainContact` int(10) unsigned NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `tbl_CoordinatorEAddress`
-- 

INSERT INTO `tbl_CoordinatorEAddress` VALUES (1, 1, 1, 0, '2008-02-12', '2008-02-12');
INSERT INTO `tbl_CoordinatorEAddress` VALUES (2, 1, 2, 0, '2008-02-12', '2008-02-12');
INSERT INTO `tbl_CoordinatorEAddress` VALUES (3, 1, 4, 0, '2008-02-12', '2008-02-12');
INSERT INTO `tbl_CoordinatorEAddress` VALUES (4, 1, 5, 0, '2008-02-13', '2008-02-13');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_EAddress`
-- 

CREATE TABLE `tbl_EAddress` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `eAddress` varchar(255) collate latin1_general_ci NOT NULL,
  `eAddressType` enum('Telephone','Mobile','E-Mail','URL','VoIP') collate latin1_general_ci NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;

-- 
-- Dumping data for table `tbl_EAddress`
-- 

INSERT INTO `tbl_EAddress` VALUES (1, '01912332288', 'Telephone', '2008-02-12', '2008-02-12');
INSERT INTO `tbl_EAddress` VALUES (2, 'Not Given', 'Mobile', '2008-02-12', '2008-02-12');
INSERT INTO `tbl_EAddress` VALUES (3, 'phil@city-church.co.uk', 'E-Mail', '2008-02-12', '2008-02-12');
INSERT INTO `tbl_EAddress` VALUES (4, 'duncan@city-church.co.uk', 'E-Mail', '2008-02-12', '2008-02-12');
INSERT INTO `tbl_EAddress` VALUES (5, 'NotGiven', 'Mobile', '2008-02-13', '2008-02-13');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_Group`
-- 

CREATE TABLE `tbl_Group` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `tbl_Group`
-- 

INSERT INTO `tbl_Group` VALUES (1, 'City Church', '2008-02-12', '2008-02-13');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_GroupCoordinator`
-- 

CREATE TABLE `tbl_GroupCoordinator` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `groupID` int(10) unsigned NOT NULL,
  `coordinatorID` int(10) unsigned NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `tbl_GroupCoordinator`
-- 

INSERT INTO `tbl_GroupCoordinator` VALUES (1, 1, 1, '2008-02-12', '2008-02-12');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_GroupEAddress`
-- 

CREATE TABLE `tbl_GroupEAddress` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `groupID` int(10) unsigned default NULL,
  `eAddressID` int(10) unsigned default NULL,
  `isMainContact` int(10) unsigned default NULL,
  `createDate` date default NULL,
  `editDate` date default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `tbl_GroupEAddress`
-- 

INSERT INTO `tbl_GroupEAddress` VALUES (1, 1, 1, NULL, '2008-02-12', '2008-02-12');
INSERT INTO `tbl_GroupEAddress` VALUES (2, 1, 2, NULL, '2008-02-12', '2008-02-12');
INSERT INTO `tbl_GroupEAddress` VALUES (3, 1, 3, NULL, '2008-02-12', '2008-02-12');
INSERT INTO `tbl_GroupEAddress` VALUES (4, 1, 5, NULL, '2008-02-13', '2008-02-13');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_GroupPAddress`
-- 

CREATE TABLE `tbl_GroupPAddress` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `pAddressID` int(10) unsigned NOT NULL,
  `groupID` int(10) unsigned NOT NULL,
  `isResidence` tinyint(3) unsigned NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `tbl_GroupPAddress`
-- 

INSERT INTO `tbl_GroupPAddress` VALUES (1, 1, 1, 1, '2008-02-12', '2008-02-12');

-- --------------------------------------------------------

-- 
-- Table structure for table `tbl_PAddress`
-- 

CREATE TABLE `tbl_PAddress` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `organisation` varchar(255) collate latin1_general_ci default NULL,
  `line1` varchar(255) collate latin1_general_ci NOT NULL,
  `line2` varchar(255) collate latin1_general_ci default NULL,
  `line3` varchar(255) collate latin1_general_ci default NULL,
  `line4` varchar(255) collate latin1_general_ci default NULL,
  `town` varchar(255) collate latin1_general_ci NOT NULL,
  `county` varchar(255) collate latin1_general_ci NOT NULL,
  `countryID` int(10) unsigned NOT NULL,
  `postcode` varchar(15) collate latin1_general_ci NOT NULL,
  `createDate` date NOT NULL,
  `editDate` date NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `tbl_PAddress`
-- 

INSERT INTO `tbl_PAddress` VALUES (1, 'City Church', 'The Castlegate Centre ', 'Melbourne Street', '', '', 'NEWCASTLE UPON TYNE', 'Tyne and Wear', 78, 'NE1 2JQ', '2008-02-11', '2008-02-11');

See the original post for the troublesome query.

Cheers
nathj

Multiple joins returning too many rows

Multiple joins returning too many rows

Comment

Comment

Comment

Comment

Comment

Comment