I need to generate a dynamic report through JasperSoft, the result of querying three MySQL tables. The tables are:
CREATE TABLE IF NOT EXISTS 'tcc'.'Catalogo' (
'idCatalogo' INT NOT NULL,
'titulo' LONGTEXT NULL,
'Autor' LONGTEXT NULL,
'Editora' LONGTEXT NULL,
'anoPublicacao' INT NULL,
PRIMARY KEY ('idCatalogo'))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS 'tcc'.'Curso' (
'idCurso' INT NOT NULL,
'nomeCurso' VARCHAR(45) NULL,
'tipoCurso' VARCHAR(45) NULL,
'vagas' INT NULL,
PRIMARY KEY ('idCurso'))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS 'tcc'.'Referencias_adotadas' (
'idReferencia' INT NOT NULL,
'tipoReferencia' INT NOT NULL,
'Titulo' LONGTEXT NULL,
'Autor' LONGTEXT NULL,
'Editora' LONGTEXT NULL,
'anoPublicacao' INT NULL,
'Disciplina_idDisciplina' INT NOT NULL,
PRIMARY KEY ('idReferencia'),
INDEX 'fk_Referencias_adotadas_Disciplina1_idx' ('Disciplina_idDisciplina' ASC),
CONSTRAINT 'fk_Referencias_adotadas_Disciplina1'
FOREIGN KEY ('Disciplina_idDisciplina')
REFERENCES 'tcc'.'Disciplina' ('idDisciplina')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
The idea is to create the following fields:
Titulo|Autor|Quantidade de exemplares|Vagas|Vagas/exemplares
Where title and author get from referencias_adotadas
. The following separate query works:
SELECT titulo, autor FROM referencias_adotadas
Where idCurso = idCurso(Parâmetro criado no jasper)
Number of copies fetches the titles and authors in the catalogo
table and counts them. The following code, done separately, works:
select count(*) as 'quantidade de exemplares' from catalogo
where titulo = 'Parametro Jasper' and autor = 'ParametroJasper';
Jobs search the number of vacancies per course:
SELECT vagas FROM Curso where idCurso = parametrojasper;
And vacancies / copies is equal to the number of vacancies divided by the number of copies.
I'm having trouble generating a single query that encompasses all of this. If anyone can help me, thank you.