I have a data table with a few fields. id (primary key), summary_date, realm, mou and tab. For this query, the only fields I'm interested in are summary_date, realm and mou. Here's the table structure:'
Code: Select all
CREATE TABLE `peerReport` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`summary_date` date NOT NULL,
`realm` varchar(127) NOT NULL,
`mou` int(10) UNSIGNED NOT NULL,
`tab` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3226 ;Code: Select all
INSERT INTO `peerReport` (`id`, `summary_date`, `realm`, `mou`, `tab`) VALUES
(1, '2009-06-24', 'aaacom', 17072, 'term'),
(2, '2009-06-24', 'backbonecom', 82269, 'term'),
(3, '2009-06-24', 'telco1', 4166, 'term'),
(4, '2009-06-24', 'zeuscom', 2112, 'term'),
(5, '2009-06-25', 'aaacom', 8788, 'term'),
(6, '2009-06-25', 'backbonecom', 145, 'term'),
(7, '2009-06-25', 'telco1', 5877, 'term'),
(8, '2009-06-25', 'zeuscom', 3243, 'term'),
(9, '2009-06-26', 'queuetel', 5434, 'term'),
(10, '2009-06-26', 'othertel', 15545, 'term'),
(11, '2009-06-26', 'telco1', 7841, 'term'),
(12, '2009-06-27', 'aaacom', 7763, 'term'),
(13, '2009-06-24', 'aaacom', 233, 'orig');Code: Select all
REALM 2009-06-24 2009-06-25 2009-06-26
______________________________________________________
aaacom 17072 8788 NULL
backbonecom 82269 145 NULL
telco1 4166 5877 7841
zeuscom 2112 3243 NULL
queuetel NULL NULL 5434
othertel NULL NULL 15545Here is the query I have so far:
Code: Select all
SELECT
p.realm,
a.mou AS '2009-06-24',
b.mou AS '2009-06-25',
c.mou AS '2009-06-26'
FROM peerReport p
JOIN peerReport a ON p.realm=a.realm AND a.summary_date = '2009-06-24'
JOIN peerReport b ON p.realm=b.realm AND b.summary_date = '2009-06-25'
JOIN peerReport c ON p.realm=c.realm AND c.summary_date = '2009-06-26'
WHERE
p.tab = 'term'But it produces far too many results, some of which contain unwanted (wrong) data.
So, what I'm looking for is a way to modify that query (or a new query) so that it only produces one line per 'realm' and will only give results that have a 'cat' value of 'term' for the correct date (and in the correct date column).
-- Thanks, McMurphy