(Resolvido) Ajuda: Select com produto cartesiano

[thiago.mac]

Ola pessoal, estou precisando fazer um select aqui, mas não estou conseguindo

Minhas tabelas são

Tabela Objeto

Inventario (PK) int

Descricao varchar

Nome varchar

....

Tabela Rel_Objeto_Material

RID(PK)

Inventario (FK)

IdMaterial (FK)

Num - integer (com os valores 1,2,3)

Tabela Material

IdMaterial (PK)

DescMaterial

Complemento

Cada objeto pode ter 3 materiais diferentes, que estao associados na tabela rel_obj_mat

Preciso fazer um select que consiga para cada objeto, relacionar os seus 3 materiais possiveis, precisaria que fosse em apenas uma query.

Da forma como estou fazendo consigo apenas um material em cada consulta, sendo que este repete os objetos variando o material.

objeto.`INVENTARIO` ,

objeto.`DESCRICAO` ,

material.`DESCRICAO`,

FROM

objeto,

rel_objeto_material,

material,

WHERE

rel_objeto_material.IdMaterial = material.IdMaterial and

rel_objeto_material.INVENTARIO = objeto.INVENTARIO;

A resposta que recebo é assim:

Inventario Descricao Material

1 - Relogio - Madeira

1 - Relogio - Vidro

1 - Relogio - Metal

2 - Mesa - Metal

2 - Mesa - Vidro

Preciso de alguma coisa do tipo

1 - Relogio - Madeira, Vidro, Metal

2 - Mesa - Metal, Vidro

Vlw, obrigado

[Denis Courcy]

Oi, 'thiago.mac'

Esta solução é obtida através de programação. Com base nisso criei uma procedure que armazenará os dados em uma tabela temporária que será criada e colocada na memória.

Tudo o que você tem que fazer é chamar a procedure, usar um select na tabela "listagem" (nome que dei a tabela) e, depois de terminar, dar um DROP na tabela listagem para retirá-la da memória. Assim:

CALL lista_materiais;
SELECT * FROM listagem;
<<<seu código>>>
DROP TABLE listagem;

Segue abaixo o código da storage procedure:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `test`.`lista_materiais`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `lista_materiais`()
BEGIN
   DECLARE m_inventario   INTEGER;
   DECLARE old_inventario INTEGER;
   DECLARE m_descricao    CHAR(60);
   DECLARE m_descmaterial CHAR(60);
   DECLARE pos_coluna     INTEGER;

   DECLARE m_eof INTEGER DEFAULT 0;
   
   DECLARE cur_rel_objeto_material CURSOR FOR 
      SELECT rom.inventario, o.descricao, m.descmaterial 
      FROM rel_objeto_material rom 
      INNER JOIN objeto o ON o.inventario = rom.inventario 
      INNER JOIN material m ON m.idmaterial = rom.idmaterial;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET m_eof = 1; 

   CREATE TEMPORARY TABLE listagem (
      Inventario integer,
      DescObjeto varchar(60),
      DescMater1 varchar(60),
      DescMater2 varchar(60),
      DescMater3 varchar(60),
      PRIMARY KEY (Inventario)
   );
   OPEN cur_rel_objeto_material; 
   FETCH cur_rel_objeto_material INTO m_inventario, m_descricao, m_descmaterial;
   WHILE NOT m_eof DO
      SET old_inventario = m_inventario;
      SET pos_coluna = 1;
      WHILE (old_inventario = m_inventario) AND (NOT m_eof) DO
         IF pos_coluna = 1 THEN
            INSERT INTO listagem(Inventario, DescObjeto, DescMater1)
            VALUES (m_inventario, m_descricao, m_descmaterial);
         ELSEIF pos_coluna = 2 THEN
            UPDATE listagem
            SET DescMater2 = m_descmaterial
            WHERE Inventario = m_inventario;
         ELSEIF pos_coluna = 3 THEN
            UPDATE listagem
            SET DescMater3 = m_descmaterial
            WHERE Inventario = m_inventario;   
         END IF;
         SET pos_coluna = pos_coluna + 1;
         FETCH cur_rel_objeto_material INTO m_inventario, m_descricao, m_descmaterial;
      END WHILE;
   END WHILE;
   CLOSE cur_rel_objeto_material;
END$$

DELIMITER;$$

[thiago.mac]

Vlw pela ideia

Algumas pessoas me sugeriram INNER JOIN da forma como esta colocado na sua stored procedure, mas isto não resolveu apenas uniu todas as tabelas.

Estou utilizando Java + IReport, então este select ficava no relatorio que pelo que eu sei so aceita 1 query (portanto queria resolver isto em uma query), ate agora so sei fazer desta forma no IReport.

já ouvi falar que tem como passar somente os resultados da query para o IReport, mas ainda não consegui fazer desta forma

Na verdade, nunca usei stored procedure, onde armazeno esta procedure ? Como eu chamo lá no código ? Meu banco de dados tah em outra maquina, o que preciso mudar ? Se bem que acho que deveria armazenar a procedure lá mesmo não é? ?

No banco de dados tem outros campos em que devera acontecer a mesma coisa, tais como autor e tecnica, poderiamos parametrizar a stored procedure ?

Estou usando o MySQLServer 5, MySQL Administrator, etc

Vlw

[thiago.mac]

Resolvido

Acabei utilizando um subrelatório, com isto não preciso fazer tudo numa unica consulta ou mesmo usar stored procedure.