mysql join 4 tables confuse cells in rows

Question

SELECT service.name, operator.fio AS op_fio, client.fio AS cl_fio, MAX(done.day) FROM done JOIN operator ON done.id_oper = operator.id JOIN service ON done.id_serv = service.id JOIN client ON done.id_client = client.id GROUP BY service.name;

it is not clear why the cells (underlined in red) having a connection (join) with the marked red in the table "done" are highlighted, and the cells that have a connection (join) with the marked green are not displayed. How to solve this problem, what am I doing wrong?

retvizan retvizan 579 3 silver marks 5 bronze marks · Accepted Answer · 2016-11-03T12:22:11

it is not clear why the cells are highlighted (underlined in red)

because operator.fio AS op_fio, client.fio AS cl_fio are used without group functions and grouping is not done on them. The MySQL server selects a random value for them from the group. Standard such requests are generally prohibited.

You can, for example, in the subquery, select Id Services and the maximum date of the call, for them to pull the operator and client. Other ways with examples are in this article.

Answer 2 · 2016-11-02T19:21:52

Everything works correctly.

You have made GROUP BY service.name and it turns out that there will be only 1 entry for one Operator and Client.

Since you have a MAX(done.day) , the maximum value is taken 2013.04.07

here you need to change the request itself:

 SELECT service.name, operator.fio AS op_fio, client.fio AS cl_fio, MAX(done.day) FROM done LEFT JOIN operator ON done.id_oper = operator.id LEFT JOIN service ON done.id_serv = service.id LEFT JOIN client ON done.id_client = client.id GROUP BY service.name, done.id_serv, done.id_oper, done.id_client;

something like that

UPDATE

Reply to comment

 SELECT service.name, operator.fio AS op_fio, client.fio AS cl_fio, FROM done INNER JOIN (SELECT id_serv, MAX(day) AS MaxDay FROM done GROUP BY id_serv) groupedDone ON done.id_serv = groupedDone.id_serv AND done.day= groupedDone.MaxDay LEFT JOIN operator ON done.id_oper = operator.id LEFT JOIN service ON done.id_serv = service.id LEFT JOIN client ON done.id_client = client.id;

we need to get the name of the service the date it last worked, which client and operator it last worked with, and so on for each service.
You did not indicate in the request that you need to take that operator from which date was last in service.

mysql join 4 tables confuse cells in rows

2 answers 2

More articles: