Relate and compare Mysql and VB6

Question

They gave me a system of a company to update, they have 3 tables Mysql 'vendaesc, sale, venda_forma_pagamento'. From what I understand the vendaesc table serves to store the items of sale.

I need to create a report that brings me the data of the product sold, quantity sold, unit value, total sale value, form of payment, installments and the problem comes, often in a single sale insert numerous products, But there is only one form of payment for the sale. Payment method information is saved in the Sale table.

The structure of the tables: Sale

`id` int(11) NOT NULL AUTO_INCREMENT,
`Cod` varchar(10) NOT NULL DEFAULT '',
`Data` date DEFAULT NULL,
`Hora` varchar(50) NOT NULL DEFAULT '',
`Desconto` varchar(20) NOT NULL DEFAULT '',
`TotalS` double(10,2) NOT NULL,
`TotalC` double(10,2) NOT NULL,
`Cod_Cliente` varchar(20) NOT NULL DEFAULT '',
`Nome_Cliente` varchar(150) NOT NULL DEFAULT '',
`Mes` varchar(50) NOT NULL DEFAULT '',
`Ano` varchar(4) NOT NULL DEFAULT '',
`forma_pagamento` int(11) DEFAULT NULL,
`forma_pagamento_detalhe` int(11) DEFAULT NULL,
`forma_pagamento_detalhe_nome` varchar(40) NOT NULL DEFAULT '',
`forma_pagamento_parcelas` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)

Vendaesc

`Cod` varchar(20) NOT NULL DEFAULT '',
`Venda` varchar(10) NOT NULL DEFAULT '',
`CodTipo` varchar(10) NOT NULL DEFAULT '',
`Tipo` varchar(50) NOT NULL DEFAULT '',
`ObsTipo` varchar(50) NOT NULL DEFAULT '',
`CodProd` varchar(20) NOT NULL DEFAULT '',
`Prod` varchar(50) NOT NULL DEFAULT '',
`referencia` varchar(50) NOT NULL DEFAULT '',
`ValUniProd` varchar(15) NOT NULL DEFAULT '',
`UniProd` varchar(5) NOT NULL DEFAULT '',
`QuantidadeProd` varchar(10) NOT NULL DEFAULT '',
`ValorProd` varchar(20) NOT NULL DEFAULT '',
`Maquina` varchar(50) NOT NULL DEFAULT '',
`Cod_Cliente` varchar(50) NOT NULL DEFAULT '',
`Cliente` varchar(250) NOT NULL DEFAULT '',
`Data` date DEFAULT NULL,
`Mes` varchar(45) NOT NULL DEFAULT '',
`Ano` varchar(4) NOT NULL DEFAULT '',
`Cod_vendedor` varchar(15) NOT NULL DEFAULT '',
`Nome_Vendedor` varchar(155) NOT NULL DEFAULT '',
PRIMARY KEY (`Cod`)

Forma Pagamento

`cod` varchar(20) NOT NULL DEFAULT '',
`descricao` varchar(255) NOT NULL DEFAULT '',

Sql that I tried, 20% of the records returned with wrong data relative to the same or different

SQL = "SELECT *,
IF(venda.cod = vendaesc.Venda and venda.forma_pagamento > 1,'IGUAL','DIFERENTE') AS resultado_agrupa,
venda.TotalS AS total_venda_agrupa,
venda_forma_pagamento.descricao AS nome_forma_pagamento,
vendaesc.QUANTIDADEprod as qtd, 
(vendaesc.QUANTIDADEprod * vendaesc.ValUniProd) As total_soma

FROM vendaesc,venda,venda_forma_pagamento

where vendaesc.data='" & DateRat & "' AND
venda.cod = vendaesc.venda AND venda.forma_pagamento =
venda_forma_pagamento.cod  order by venda.cod"

Programming is VB6 :(

SQL:

Sqlfiddle

Answer 1

Reading your question and especially this comment:

Oops, the idea would be to generate a report listing all the items from vendaesc, as there may be several items in vendaesc for a sale, I need the report to inform me that these certain items are part of the same sale, showing to the user if the same sale to EQUAL, if not DIFFERENT

And I’d say you’re trying to do the wrong thing in yours SELECT you try to put a IF it will compare the value of column 1 with column 2 of the same row. In your case it returns different only when the form of payment is greater than a (venda.forma_pagamento > 1). Give a check that you will see that’s why. I say this because, if you make a JOIN in venda and vendaesc for cod equal to venda (AND venda.cod = vendaesc.venda) and in the SELECT you put IF(venda.cod = vendaesc.Venda so this one IF it will always be true, because if it were false it would not appear, because the WHERE is forcing only those who are true to come. Understood?

With SQL below you can see the column cod with the column venda always equal, is the same WHERE of yours, but with the SELECT changed to make it easier to see the result. Note the first two columns. FIDDLE

SELECT venda.cod, vendaesc.venda, venda.forma_pagamento, vendaesc.codprod
  FROM vendaesc,
       venda,
       venda_forma_pagamento 
 where vendaesc.data='2015-11-12' 
   AND venda.cod = vendaesc.venda 
   AND venda.forma_pagamento = venda_forma_pagamento.cod  
 order by venda.cod

Cod Venda   forma_pagamento CodProd
000001  000001  1   00002
000002  000002  1   00002
000003  000003  1   00001
000004  000004  4   00001
000004  000004  4   00058
000004  000004  4   00067
000005  000005  1   00001
000005  000005  1   00002

To strengthen we will include your IF in the SELECT, but only the first part: FIDDLE

SELECT IF(venda.cod = vendaesc.Venda,'IGUAL','DIFERENTE'),
        venda.cod, vendaesc.venda, venda.forma_pagamento, vendaesc.codprod
  FROM vendaesc,
       venda,
       venda_forma_pagamento 
 where vendaesc.data='2015-11-12' 
   AND venda.cod = vendaesc.venda 
   AND venda.forma_pagamento = venda_forma_pagamento.cod  
 order by venda.cod

IF(venda.cod = vendaesc.Venda,'IGUAL','DIFERENTE')  Cod Venda   forma_pagamento CodProd
IGUAL   000001  000001  1   00002
IGUAL   000002  000002  1   00002
IGUAL   000003  000003  1   00001
IGUAL   000004  000004  4   00067
IGUAL   000004  000004  4   00001
IGUAL   000004  000004  4   00058
IGUAL   000005  000005  1   00002
IGUAL   000005  000005  1   00001

I don’t need to say that if you put the second part of IF in the SELECT all records that have form of payment 1 will come with the result DIFERENTE in the IF. Agrees?

What I suggest in your case is, believing you are using crystal report, is to make the following SQL: (The site is saying that I need 10 reputation points to publish more than 2 links, I do not understand why this, so I will not put the fiddle of this SLQ, but it works on your fiddle same, sorry for that)

SELECT vendaesc.*, venda.forma_pagamento
  FROM vendaesc,
       venda,
       venda_forma_pagamento 
 where vendaesc.data='2015-11-12' 
   AND venda.cod = vendaesc.venda 
   AND venda.forma_pagamento = venda_forma_pagamento.cod  
 order by venda.cod

Where it returns all the columns you need with the WHERE and in the report you create a grouping with the sales code, with this all items sold organized by sales.

The most important thing in that answer is that you understand yours IF in the SELECT, it does not compare two different lines in the result, it is a IF for each line.

PS: changes the vendaesc.* for a list of all columns, it would be a good practice not to use the *

Answer 2

I believe I understand what you want, I would like to display all the records (one in each row) according to the amount of products sold displaying the complete sales information.

if yes, try this query:

SELECT * FROM venda v
LEFT JOIN vendaesc vesc ON v.Cod = vesc.venda
LEFT JOIN venda_forma_pagamento vfp ON vfp.cod = v.forma_pagamento
LEFT JOIN (SELECT Venda, SUM(CAST(REPLACE(ValorProd, ",", ".") AS DECIMAL(10,2))) as TOTALVALORPRDUTO FROM vendaesc GROUP BY Venda) as total ON total.Venda = v.Cod

I would like to point out that it is not good practice to use VARCHAR to store monetary values, also avoid FLOAT can be dangerous. recommended is DECIMAL(10,2) for example.

** I had to add a POG to convert the prod value to decimal and replace comma by point, because in the schema is like varchar.