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
