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?
Aucun commentaire:
Enregistrer un commentaire