Doubt in Mysql query

Question

Query that I am 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 took:

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: Unite 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’s happening is that when I run this query, instead of returning me the expected, returns me empty, already tried to use left join, but only returned me a single record.

Note: In charge inner join lo.login_id = so.os_advogado, the field login_id receives the ID user as well as in lawyer

Answer 1

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

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

For INSERT that you presented you have only the table records os, therefore the INNER JOIN no result. You can see in the question "What is the difference between INNER JOIN and OUTER JOIN?" what this really impacts, but for what you presented it will be necessary to use a LEFT JOIN.

... I tried to use left Join but only returned me a single record.

When you use an aggregation function (in your case the function SUM) it is necessary to inform which are the grouping attributes (GROUP BY), otherwise, in the case of MySQL, will always be shown only one line, adding ALL results. In your case everything indicates that you want to group by order of service, therefore the query resulting would be 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

Answer 2

It does not return any result because for the examples of the past tables, I see no correlation between the related fields in the following portion of the research:

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