Issue advanced MYSQL queries

Question

So, I have a question, how to make a query related between 3 tables, being:

Tabela 1:

Nome: tb_os    
Campos: id_os, id_cliente, valor_os    

Tabela 2:

Nome: tb_clientes    
Campos: id_cliente, endereco_cliente, tel_cliente


Tabela 3:

Nome: tb_status_os    
Campos: id_status_os, id_os, status_os

Table number 3, will contain at least 3 status tied to one, can be: Open, Bench, Released.

Which doubt?

I need to make a query relating the 3 tables, however, because of the table tb_status_os, will be returned for each query 3 lines or records, what I need is to group in a row for each O.S. (table 1), and the result brought from table 3, is only the last, being the current status.

I tried so many ways:

SELECT * FROM tb_os AS a

INNER JOIN tb_clientes AS b USING(id_os)

INNER JOIN tb_status_os AS c ON  c.ids_os =

(

    SELECT MAX(id_os) FROM tb_status_os

    ORDER BY id_status_os DESC

)

GROUP BY a.id_os;

Returns the records grouped correctly, but does not return the last record of table 3 ( ex.: Released), for each 1 of table 1, always returns the first of table 3 (e.g.: Open).

Could you help me in this sense?

Thank you!

Answer 1

To solve this type of problem in a clear way it is necessary to cross with the table status_os twice, because?

In the first step you will bring all the fields and bring the MAX(cd_status) at this stage if you already bring the description the GROUP BY won’t work:

  SELECT a.id_os, a.id_cliente, cli.endereco_cliente, cli.tel_cliente,
  a.valor_os, MAX(sta.id_status_os) AS cd_ultimo_status

  FROM tb_os AS os

  INNER JOIN tb_clientes AS cli
    ON cli.id_cliente = os.id_cliente

  INNER JOIN tb_status_os AS sta
    ON sta.id_os = os.id_os

  GROUP BY a.id_os, a.id_cliente, cli.endereco_cliente, cli.tel_cliente,
  a.valor_os

After that use the concept of SUBSELECT (sub query) to cross again with the table status_os only to bring the description of the status of the:

SELECT res.* sta_desc.status_os
FROM (SELECT a.id_os, a.id_cliente, cli.endereco_cliente, cli.tel_cliente,
      a.valor_os, MAX(sta.id_status_os) AS cd_ultimo_status

      FROM tb_os AS os

      INNER JOIN tb_clientes AS cli
        ON cli.id_cliente = os.id_cliente

      INNER JOIN tb_status_os AS sta
        ON sta.id_os = os.id_os

      GROUP BY a.id_os, a.id_cliente, cli.endereco_cliente, cli.tel_cliente,
      a.valor_os
      
) res

INNER JOIN tb_status_os AS sta_desc
        ON sta_desc.id_os = res.id_os

I simulated your case in a simple way for you to better understand the concept:

SELECT tot.*, sta.status

FROM (
      SELECT res.cd_os, MAX(cd_status) AS cd_status
      FROM (SELECT 1 as cd_os, 1 AS cd_status, 'aberto' AS status
            FROM DUAL 
            UNION ALL
            SELECT 1 as cd_os, 2 AS cd_status, 'fechado' AS status
            FROM DUAL ) res
      GROUP BY res.cd_os) tot


INNER JOIN (SELECT 1 AS cd_status, 'aberto' AS status
            FROM DUAL 
            UNION ALL
            SELECT 2 AS cd_status, 'fechado' AS status
            FROM DUAL) sta
            
ON sta.cd_status = tot.cd_status

In the first SELECT brings the two values, in the second I bring the MAX(cd_status), cryo-um SUBSELECT and then cross with the table Sta to bring the description.

Note: Whenever create one SUBSELECT who need to cross the same table several times, use different nicknames for the "same" table to avoid conflicts.

Answer 2

Good partner, there’s a way that I’ve ridden here for other situations that should suit your :)

uses group_concat and poe an order by id desc, and uses substring_index to get the first :D record The cat jump is this here: SUBSTRING_INDEX(GROUP_CONCAT(status_os order by id_status_os desc),',',1)

SELECT 
a.*, 
b.*,
SUBSTRING_INDEX(GROUP_CONCAT(status_os order by id_status_os desc),',',1) as status_atual
FROM tb_os AS a
INNER JOIN tb_clientes AS b USING(id_cliente)
INNER JOIN tb_status_os AS c ON  USING(c.ids_os) 
GROUP BY a.id_os;