I need to run a select in Mantis DB.
After many tests my solution was create a temporary table end than run the select:
Code: Select all
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (
SELECT @bug_id:=b.id as id,
t.description AS descricao,
additional_information AS observacoes,
c.name AS categoria,
GROUP_CONCAT((CASE field_id WHEN 24 THEN s.value ELSE NULL END)) AS data_inicio,
GROUP_CONCAT((CASE field_id WHEN 25 THEN s.value ELSE NULL END)) AS data_fim,
GROUP_CONCAT((CASE field_id WHEN 34 THEN s.value ELSE NULL END)) AS servidor,
GROUP_CONCAT((CASE field_id WHEN 28 THEN s.value ELSE NULL END)) AS impacto,
GROUP_CONCAT((CASE field_id WHEN 30 THEN s.value ELSE NULL END)) AS sistema,
(SELECT DISTINCT CONCAT( '//images//uploads//', diskfile ) FROM mantis_bug_file_table WHERE bug_id = @bug_id ORDER BY id ASC LIMIT 0, 1) AS print1,
(SELECT DISTINCT CONCAT( '//images//uploads//', diskfile ) FROM mantis_bug_file_table WHERE bug_id = @bug_id ORDER BY id ASC LIMIT 1, 1) AS print2,
(SELECT DISTINCT CONCAT( '//images//uploads//', diskfile ) FROM mantis_bug_file_table WHERE bug_id = @bug_id ORDER BY id ASC LIMIT 2, 1) AS print3
FROM mantis_bug_table b
INNER JOIN mantis_bug_text_table t ON b.id=t.id
INNER JOIN mantis_category_table c ON b.category_id=c.id
INNER JOIN mantis_custom_field_string_table s ON b.id=s.bug_id
WHERE b.project_id = 1
GROUP BY b.id
);
SELECT * FROM temp_table
Is my query ok?