MySQL and JasperSoft

1

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.

    
asked by anonymous 08.07.2018 / 00:13

0 answers