How do I create a view in mysql by taking data from 4 or more different tables?

Question

How can I create a view and show the data as below:

Answer 1

create view minha_view as 
select e.id, e.razao_social, i1.qtd as ensinando_a_contar_qtd, i2.qtd as matematica_qtd, 
    i3.qtd as fisica_qtd, i4.qtd as quimica_qtd, i5.qtd as historia_qtd, 
    i6.qtd as computacao_qtd, i7.qtd as logica_qtd, n.data_compra
from Escola e
inner join Notafiscal n on e.id = n.escola_id
inner join Itemnotafiscal i1 on n.id = i1.notafiscal_id and i1.livro_id = 1
inner join Itemnotafiscal i2 on n.id = i2.notafiscal_id and i2.livro_id = 2
inner join Itemnotafiscal i3 on n.id = i3.notafiscal_id and i3.livro_id = 3
inner join Itemnotafiscal i4 on n.id = i4.notafiscal_id and i4.livro_id = 4
inner join Itemnotafiscal i5 on n.id = i5.notafiscal_id and i5.livro_id = 5
inner join Itemnotafiscal i6 on n.id = i6.notafiscal_id and i6.livro_id = 6
inner join Itemnotafiscal i7 on n.id = i7.notafiscal_id and i7.livro_id = 7

Answer 2

The syntax for creating a view in Mysql is:

CREATE VIEW nome_da_view AS
SELECT (...)

In the SELECT you use INNER JOIN to make the tables join and ready! Then just give a select in the view:

SELECT * FROM nome_da_view

View documentation: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Answer 3

That would be the encoding SQL for such a result:

Example: Sqlfiddle

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
          'MAX(IF(nome = ''',nome,''', itemnotafiscal.qtde, NULL)) AS ', 
           concat(replace(lower(nome), ' ', '_'), '_qtd')
        )) INTO @sql FROM livro;

SET @sql = CONCAT('SELECT escola.id, escola.cnpj, 
                  escola.razao_social, ', @sql ,',
                  date_format(notafiscal.data_compra, "%d/%m/%Y")
                  as data_compra
           FROM escola
           INNER JOIN notafiscal on notafiscal.escola_id = escola.id
           INNER JOIN itemnotafiscal on itemnotafiscal.notafiscal_id = notafiscal.id
           INNER JOIN livro on livro.id = itemnotafiscal.livro_id
           GROUP BY escola.id
           ORDER BY escola.id, livro.nome');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I would advise you instead of creating a view, create a Procedure this way, for the simple fact that when inserting new books it becomes dynamic, having little maintenance.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `testdb`.`spExemplo` ()
BEGIN
    SET @sql = NULL;

    SELECT  GROUP_CONCAT(DISTINCT CONCAT(
          'MAX(IF(nome = ''',nome,''', itemnotafiscal.qtde, NULL)) AS ', 
           concat(replace(lower(nome), ' ', '_'), '_qtd')
        )) INTO @sql FROM livro;

    SET @sql = CONCAT('SELECT escola.id, escola.cnpj, escola.razao_social,
           notafiscal.data_compra, ', @sql ,'
           FROM escola
           INNER JOIN notafiscal on notafiscal.escola_id = escola.id
           INNER JOIN itemnotafiscal on itemnotafiscal.notafiscal_id = notafiscal.id
           INNER JOIN livro on livro.id = itemnotafiscal.livro_id
           GROUP BY escola.id
           ORDER BY escola.id, livro.nome');

    PREPARE stmt FROM @sql0;
    EXECUTE stmt;
END