Get data from MySQL DB for IsoQuant.

getisoquantdata.sql

DROP TABLE IF EXISTS petides_for_alignment;
CREATE TABLE petides_for_alignment
SELECT
	p.`workflow_index` as run,
	p.`mass` as pep_mass,
	p.`sequence`,
	p.`type`,
	p.`modifier`,
	p.`score`,
	q.intensity,
	l.charge,
	l.mass as le_mass,
	l.retention_time as rt,
	m.Mobility as dt
FROM
	`peptide` as p
	join query_mass as q ON p.`query_mass_index`=q.`index`
	join low_energy as l ON q.`low_energy_index`=l.`index`
	join mass_spectrum as m USING(low_energy_index)
GROUP By l.`index`;


DROP TABLE IF EXISTS all_signals;
CREATE TABLE all_signals_for_alignment
SELECT
	m.workflow_index,
	m.mass,
	m.intensity,
	m.RT,
	m.Mobility,
	pa.*
FROM
	mass_spectrum as m
	left join
	`petides_for_alignment` as pa ON 
		pa.workflow_index = m.workflow_index 	AND
		pa.mass = m.mass 						AND 
		pa.RT = m.RT 							AND 
		pa.dt = m.mobility
;