// 1. snapshot left join 
select /*B.modulePath, B.fileName*/
toolName, language, checkerCode, severityCode, statusCode, createdDateTime, modifiedDateTime,
avgComplexity, maxComplexity, minComplexity, loc, cloc, sloc, classCount, methodCount, commentRatio
from
(select * from
(select
    modulePath, fileName,
    sum(if(metricName = 'avgComplexity', metricValue, 0)) as avgComplexity,
    sum(if(metricName = 'maxComplexity', metricValue, 0)) as maxComplexity,
    sum(if(metricName = 'minComplexity', metricValue, 0)) as minComplexity,
    sum(if(metricName = 'loc', metricValue, 0)) as loc,
    sum(if(metricName = 'cloc', metricValue, 0)) as cloc,
    sum(if(metricName = 'sloc', metricValue, 0)) as sloc,
	sum(if(metricName = 'classCount', metricValue, 0)) as classCount,
	sum(if(metricName = 'methodCount', metricValue, 0)) as methodCount,
	sum(if(metricName = 'commentRatio', metricValue, 0)) as commentRatio
 from CodeMetrics where lastYn = 'Y'
 group by fileName, modulePath) A
 where A.loc != 0) B 
left join 
Defect C
on B.fileName = C.fileName and B.modulePath = C.modulePath;
 
// 2. left join Snapshot
select
	toolName, language, checkerCode, severityCode, statusCode, createdDateTime, modifiedDateTime,
	avgComplexity, maxComplexity, minComplexity, loc, cloc, sloc, classCount, methodCount, commentRatio
from
	Defect C left join 
	(select * from
			(selectSc
				modulePath, fileName,
				sum(if(metricName = 'avgComplexity', metricValue, 0)) as avgComplexity,
				sum(if(metricName = 'maxComplexity', metricValue, 0)) as maxComplexity,
				sum(if(metricName = 'minComplexity', metricValue, 0)) as minComplexity,
				sum(if(metricName = 'loc', metricValue, 0)) as loc,
				sum(if(metricName = 'cloc', metricValue, 0)) as cloc,
				sum(if(metricName = 'sloc', metricValue, 0)) as sloc,
				sum(if(metricName = 'classCount', metricValue, 0)) as classCount,
				sum(if(metricName = 'methodCount', metricValue, 0)) as methodCount,
				sum(if(metricName = 'commentRatio', metricValue, 0)) as commentRatio
			 from CodeMetrics where lastYn = 'Y'
			 group by fileName, modulePath) A
 	where A.loc != 0) B 
	on B.fileName = C.fileName and B.modulePath = C.modulePath;