Here is my sql (in mysql table)
select * from(SELECT sample_register.usin,
DATE_FORMAT(sample_register.doc,'%d-%m-%Y') as doc1,
sample_register.location,
sample_register.description,
sample_register.type,
sample_allocation.gamma,
gamma_results.act,
gamma_results.act_sd,
gamma_results.mdl,
gamma_results.bdl,
DATE_FORMAT(count_dt,'%d-%m-%Y') as count_dt
FROM sample_register
LEFT JOIN sample_allocation
ON sample_register.usin=sample_allocation.usin
LEFT JOIN gamma_results
ON gamma_results.usin = sample_register.usin
AND gamma_results.istp='Cs137'
WHERE mid(sample_register.usin,3,1)='F'
AND sample_register.doc BETWEEN '2015-01-01'
AND '2015-03-31'
AND sample_register.category='ter'
AND sample_allocation.gamma='Y'
ORDER BY mid(sample_register.usin,3,1),
sample_register.doc,
sample_register.usin) AS a
LEFT JOIN (SELECT sample_register.usin,
gamma_results.act,
gamma_results.act_sd,
gamma_results.mdl,
gamma_results.bdl
FROM sample_register
LEFT JOIN gamma_results
ON gamma_results.usin = sample_register.usin
AND gamma_results.istp='k40'
WHERE mid(sample_register.usin,3,1)='F'
AND sample_register.doc
BETWEEN '2015-01-01'
AND '2015-03-31'
AND (sample_register.category='ter')
ORDER BY mid(sample_register.usin,3,1),
sample_register.doc,
sample_register.usin) AS b
ON a.usin=b.usin
There are 4 records in the gamma_results table. two records each for 10/04/2015 and 18/04/2015.
USIN istp act count_dt
-----------------------------------------
15FML002 Cs137 0.00769 10/04/15
15FML002 K40 0 10/04/15
15FML002 Cs137 0.00608 18/04/15
15FML002 K40 12.117 18/04/15
Query output data in the following form (some fields I deleted for convenience)
15FML002 0.00769 Y 10/04/15 00
15FML002 0.00769 Y 10/04/15 12.117
15FML002 0.00608 Y 18/04/15 00
15FML002 0.00608 Y 18/04/15 12.117
But I want to get output in two records. That is like this
15FML002 0.00769 Y 10/04/15 00
15FML002 0.00608 Y 18/04/15 12.117
How can I reframe (join or union) the query to get output like this? /// edited on 30/04/2015
I am unable to create an sqlfiddle because of some proplem in their site. Here is the DDL and DML for the two tables sample_register and gamma results. The sample_allocation table can be ignored at this juncture.
CREATE TABLE `sample_register` (
`usin` varchar(11) NOT NULL,
`sample_id` varchar(7) NOT NULL,
`doc` date NOT NULL,
`location` varchar(255) DEFAULT NULL,
`category` varchar(50) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`fwt` decimal(10,2) DEFAULT NULL COMMENT 'This filed contains either fwt in gms or volume in ltr for milk or volume of air for particulate',
`dwt` decimal(10,2) DEFAULT NULL,
`ashwt` decimal(10,2) DEFAULT NULL,
`user` varchar(255) DEFAULT NULL,
`to_dt` date DEFAULT NULL COMMENT 'This is for particulate sample filter removal date',
`wc` decimal(10,2) DEFAULT NULL,
`oc` decimal(10,2) DEFAULT NULL,
`ac` decimal(10,2) DEFAULT NULL,
`status` varchar(1) DEFAULT NULL,
`remarks` varchar(255) DEFAULT NULL,
PRIMARY KEY (`usin`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `sample_register` VALUES ('15FML002', 'NIL', '2015-04-09', 'MALLAPUR', 'ter', 'MILK', 'milk', '2000.00', null, null, '1604015', null, null, null, null, null, null);
DROP TABLE IF EXISTS `gamma_results`;
CREATE TABLE `gamma_results` (
`usin` varchar(255) NOT NULL,
`sysid` varchar(255) NOT NULL,
`count_time` decimal(10,0) DEFAULT NULL,
`geo` varchar(255) DEFAULT NULL,
`vol` decimal(10,2) DEFAULT NULL,
`energy` decimal(10,2) DEFAULT NULL,
`istp` varchar(255) DEFAULT NULL,
`bkg` decimal(10,5) DEFAULT NULL,
`eff` decimal(10,3) DEFAULT NULL,
`sigma` decimal(10,5) DEFAULT NULL,
`ncps` decimal(10,5) DEFAULT NULL,
`sd` decimal(10,5) DEFAULT NULL,
`mdl` decimal(10,5) DEFAULT NULL,
`act` decimal(10,5) DEFAULT NULL,
`act_sd` decimal(10,5) DEFAULT NULL,
`bdl` varchar(1) DEFAULT NULL,
`entry_time` datetime DEFAULT NULL,
`entered_by` int(11) DEFAULT NULL,
`count_dt` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `gamma_results` VALUES ('15FML002', 'HPGE2', '25000', 'nb', '1000.00', '364.48', 'I131', '0.01000', '3.400', '0.00190', '-0.01000', '0.00041', '0.06882', null, '0.00000', 'Y', '2015-04-13 10:24:11', '1619381', '2015-04-10');
INSERT INTO `gamma_results` VALUES ('15FML002', 'HPGE2', '25000', 'nb', '1000.00', '661.66', 'Cs137', '0.00020', '2.060', '0.00027', '-0.00020', '0.00006', '0.00769', null, '0.00000', 'Y', '2015-04-13 10:24:57', '1619381', '2015-04-10');
INSERT INTO `gamma_results` VALUES ('15FML002', 'HPGE2', '25000', 'nb', '1000.00', '1460.73', 'K40', '0.00500', '0.911', '0.00134', '-0.00450', '0.00032', '1.37855', null, '0.00000', 'Y', '2015-04-13 10:25:37', '1619381', '2015-04-10');
INSERT INTO `gamma_results` VALUES ('15FML002', 'HPGE2', '15000', 'nb50', '2000.00', '661.66', 'Cs137', '0.00020', '3.380', '0.00035', '-0.00020', '0.00006', '0.00608', null, '0.00000', 'Y', '2015-04-20 10:21:48', '1619381', '2015-04-18');
INSERT INTO `gamma_results` VALUES ('15FML002', 'HPGE2', '15000', 'nb50', '2000.00', '1460.73', 'K40', '0.00500', '1.550', '0.00173', '0.04008', '0.00176', '0.52302', '12.11700', '0.53200', 'N', '2015-04-20 10:23:00', '1619381', '2015-04-18');
