I'm trying to write a query that selects from four tables

• campaignSentParent csp
• campaignSentEmail cse
• campaignSentFax csf
• campaignSentSms css

Each of the cse, csf, and css tables are linked to the csp table by csp.id = (cse/csf/css).parentId

The csp table has a column called campaignId,

What I want to do is end up with rows that look like:

| id | dateSent   | emailsSent | faxsSent | smssSent |  
| 1  | 2011-02-04 | 139        | 129      | 140      |  

But instead I end up with a row that looks like:

| 1  | 2011-02-03 | 2510340    | 2510340  | 2510340  |

Here is the query I am trying

SELECT csp.id id, csp.dateSent dateSent,  
       COUNT(cse.parentId) emailsSent,  
       COUNT(csf.parentId) faxsSent,  
       COUNT(css.parentId) smsSent  
FROM   campaignSentParent csp,  
       campaignSentEmail cse,  
       campaignSentFax csf,  
       campaignSentSms css  
WHERE  csp.campaignId = 1  
AND    csf.parentId = csp.id  
AND    cse.parentId = csp.id  
AND    css.parentId = csp.id;  

---

Adding GROUP BY did not help, so I am posting the create statements.

csp

CREATE TABLE `campaignsentparent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaignId` int(11) NOT NULL,
  `dateSent` datetime NOT NULL,
  `account` int(11) NOT NULL,
  `status` varchar(15) NOT NULL DEFAULT 'Creating',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

cse/csf (same structure, different names)

CREATE TABLE `campaignsentemail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NOT NULL,
  `contactId` int(11) NOT NULL,
  `content` text,
  `subject` text,
  `status` varchar(15) DEFAULT 'Pending',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=140 DEFAULT CHARSET=latin1

css

CREATE TABLE `campaignsentsms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NOT NULL,
  `contactId` int(11) NOT NULL,
  `content` text,
  `status` varchar(15) DEFAULT 'Pending',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=141 DEFAULT CHARSET=latin1