Query in MySQL query

Navigation
2

Query that I'm using 

SELECT 
os_id,os_data,os_processo,os_solicitante,os_tipo,os_cliente,os_empresa,os_adverso,os_local,
os_comarca,os_advogado,os_preposto,os_documentos,os_status,login_nome,
sum(osh.os_honorarios_valor) as honorario,sum(osh.os_honorarios_os_despesas) as despesas from os so 
inner join os_honorarios osh on so.os_id = osh.os_honorarios_os_id inner join login lo on lo.login_id = so.os_advogado
where so.os_status !=0

Tables: 

CREATE TABLE 'os_honorarios' (
    'os_honorarios_id' INT(11) NOT NULL AUTO_INCREMENT,
    'os_honorarios_valor' FLOAT NOT NULL,
    'os_honorarios_os_id' INT(11) NOT NULL,
    'os_honorarios_os_despesas' FLOAT NULL DEFAULT NULL,
    PRIMARY KEY ('os_honorarios_id')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE 'os' (
    'os_id' INT(11) NOT NULL AUTO_INCREMENT,
    'os_data' DATE NOT NULL,
    'os_processo' VARCHAR(50) NOT NULL,
    'os_solicitante' VARCHAR(50) NOT NULL,
    'os_tipo' VARCHAR(50) NOT NULL,
    'os_cliente' VARCHAR(50) NOT NULL,
    'os_empresa' VARCHAR(50) NOT NULL,
    'os_adverso' VARCHAR(50) NOT NULL,
    'os_local' VARCHAR(50) NOT NULL,
    'os_comarca' VARCHAR(50) NOT NULL,
    'os_advogado' VARCHAR(50) NOT NULL,
    'os_preposto' VARCHAR(50) NOT NULL,
    'os_documentos' VARCHAR(50) NOT NULL,
    'os_status' INT(11) NOT NULL,
    PRIMARY KEY ('os_id')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE 'login' (
    'login_id' INT(11) NOT NULL AUTO_INCREMENT,
    'login_email' VARCHAR(150) NOT NULL DEFAULT '0',
    'login_password' VARCHAR(150) NOT NULL DEFAULT '0',
    'login_rule' VARCHAR(150) NOT NULL DEFAULT '0',
    'login_nome' VARCHAR(150) NOT NULL DEFAULT '0',
    'login_cpf' VARCHAR(50) NOT NULL,
    'login_status' INT(11) NOT NULL,
    PRIMARY KEY ('login_id')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9
;

Test I did: 

INSERT INTO os (os_id, os_data, os_processo, os_solicitante, os_tipo, os_cliente, os_empresa, os_adverso, os_local, os_comarca, os_advogado, os_preposto, os_documentos, os_status) VALUES (1, '2018-09-06', 't', 't', 't', 't', 't', 't', 't', 't', '8', 't', 't', 1), (2, '2018-09-06', 't', 't', 't', 't', 't', 't', 't', 't', '8', 't', 't', 1);

What I'm trying to do: Join these three tables and make the display. 

<th>ID</th>
<th>Data</th>
<th>processo</th>
<th>solicitante</th>
<th>cliente</th>
<th>empresa</th>
<th>advogado</th>
<th>preposto</th>
<th>despesa</th>
<th>honorarios</th>
<th>Status</th>
<th>Detalhes</th>
<th>Dispesa</th>
<th>honorarios</th>
<th>Desativar</th>

What is happening is that when I run this query , instead of returning what I expected, it returns me empty, I already tried to use left join , but only returned a single record. p>

  

Note: In the inner join lo.login_id = so.os_advogado command, the login_id field receives the ID of the user as well as the os_address

    
asked by anonymous 21.02.2018 / 02:03

2 answers

1

There are some problems with your query , the two main ones are the following:

  

What is happening is that when I execute this query instead of returning the expected returns me empty ...

For the INSERT you submitted you have only the records of the os table, so the INNER JOIN will not show any results. You can see in the question " What is the difference between INNER JOIN and OUTER JOIN? "

  

... I have tried to use left join but only a single record returned.

When you use an aggregate function (in this case the LEFT JOIN function) it is necessary to inform you what are the grouping attributes ( SUM ), otherwise, in the case of GROUP BY , always only one row will be shown , adding ALL results. In your case everything indicates that you want to group by service order, so the resulting MySQL would look similar to the following: 

SELECT os.os_id,
       os.os_data,
       os.os_processo,
       os.os_solicitante,
       os.os_tipo,
       os.os_cliente,
       os.os_empresa,
       os.os_adverso,
       os.os_local,
       os.os_comarca,
       os.os_advogado,
       os.os_preposto,
       os.os_documentos,
       os.os_status,
       lo.login_nome,
       SUM(osh.os_honorarios_valor) AS honorario,
       SUM(osh.os_honorarios_os_despesas) AS despesas
  FROM os os
       LEFT JOIN os_honorarios osh ON os.os_id = osh.os_honorarios_os_id
       LEFT JOIN login lo ON lo.login_id = os.os_advogado
 WHERE os.os_status !=0
 GROUP BY os.os_id
    
21.02.2018 / 07:08
-2

Does not return any results because because of past table examples, I see no correlation between related fields in the following portion of the search:

  

INNER JOIN login lo ON lo.login_id = os.os_advogado

    
21.02.2018 / 13:35
How can I split this INSERT in steps? How to open a PDF file, which is in the assets of my APP, in a PDF reader already installed on the mobile?