DROP TABLE IF EXISTS source_builds;

CREATE TABLE source_builds
SELECT 	t1.fsource,
		t3.fattribute_value AS build,
		CASE WHEN INSTR(t3.fattribute_value, 'NCBIM') THEN 'Mouse'
			WHEN INSTR(t3.fattribute_value, 'NCBI') THEN 'Human'
			WHEN INSTR(t3.fattribute_value, 'RGSC') THEN 'Rat'
			ELSE 'Unknown'
		END AS common_name
FROM ftype t1
INNER JOIN fdata t2 ON t1.ftypeid=t2.ftypeid
INNER JOIN fattribute_to_feature t3 ON t2.fid=t3.fid
INNER JOIN fattribute t4 ON t3.fattribute_id=t4.fattribute_id
WHERE t4.fattribute_name='Build'
GROUP BY t1.fsource, t3.fattribute_value;

