Menu
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');
307
Display Row Values as Columns in MySQL Dynamically You can customize the above query as per your requirements by adding WHERE clause or JOINS. If you want to transpose only select row values as columns, you can add WHERE clause in your 1st select GROUP_CONCAT statement.
You can use SQL statements to select rows from the database to display on your report. Selecting rows limits, or creates a subset of, the data in a table. You select rows by creating a row condition. You can select rows that have no data in a column.
If you'd like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function.
I have seen output like this before.
It is caused by a tiny error in the joining. If I remember correctly I solved this by nesting some of my joins as sub queries so I was adding each table to an existing table along a join. I probably over egged the mix but it did work nicely. (UPDATE: Yeah, I see now that is exactly what you are doing).
At some point in that mix the join has not been unique or the way you have arranged it has not been unique and so SQL has given you all the combinations it could come up with.
(Assuming, of course, that it is not just a missing GROUP BY that is upsetting things. That's the other source of problems like this.)
I will have a careful look through your SQL and see if I can spot the problem but a little trial and error would be much, much faster.
UPDATE 1: This is either a really impressive hack or an error and I cannot fathom which.
LEFT JOIN gamma_results
ON gamma_results.usin = sample_register.usin
AND gamma_results.istp='Cs137'
I have never seen a conditional as part of a join. I could not tell you if this would even work but my gut reaction is to want to put this in the WHERE clause. Feel free to take me to school on this as you seem to know what you are doing and I do not have hands on to the actual project (nor do I know everything).
Update 2: Given that your hard coded join must be close to working my feeling is that I would want a GROUP by in the nested SELECT as you are using aggregate functions (count).
I recall once making a quite complex query that when you get right down to it was effectively saying "for each table_a add table_b". Honestly make friends with GROUP BY and if that does not solve your problem then I am a small inoffensive monkey.
84
First of all you should provide same data and sqlfiddle if you need help with query for some specific dataset.
Since I have no data for the fiddle here is my guess:
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,
gr.act,
gr.act_sd,
gr.mdl,
gr.bdl,
gr.count_dt,
grK40.act
FROM sample_register
INNER JOIN sample_allocation
ON sample_register.usin=sample_allocation.usin
AND sample_allocation.gamma='Y'
LEFT JOIN (
SELECT
usin,
act,
act_sd,
mdl,
bdl,
count_dt,
DATE_FORMAT(count_dt,'%d-%m-%Y') as count_dt_formatted
FROM gamma_results
WHERE istp='Cs137'
) gr
ON gr.usin = sample_register.usin
LEFT JOIN gamma_results grK40
ON grK40.usin = gr.usin
AND grK40.istp='k40'
AND grK40.count_dt = gr.count_dt
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
But it is just a guess, because it looks very weir to me.
You wrote:
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 but in your query you use act,act_sd,mdl,bdl,DATE_FORMAT(count_dt,'%d-%m-%Y') as count_dt so we can assume that you have some other columns there like: act_sd,mdl,bdl.
Underneath you wrote: 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
Even if some field deleted, what fields are here?
Logically it is: usin,act, UNKNOWN,count_dt,UNKNOWN (equal to act when istp='K40'). but it is impossible, because you haven't such field in your query request. It seems to me that provided output was get as result of some other query, not that one you show us.
But so far, here is my guess. You are welcome if any questions.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
