Good day!

Help with the request.
There are 3 tables with data of the form:

id1 | name | 1 | var1 | 2 | var2 | id2 | id1 | note 1 | 1 | asd 2 | 1 | asd 3 | 2 | dsa id3 | id1 | text 3 | 1 | asdf 4 | 1 | fsds 5 | 2 | as44

It is necessary to select rows from the 1st table ( id1 , name ) and rows connected by id1 from the second and third tables, but in order to select from the second table the row with the maximum id2, and from the third with the maximum id3.
Accordingly, 1 record from table 1 should correspond to 1 record from table 2 and 1 record from table 3.

Can this be done with 1 query?

Thank.

    2 answers 2

    Slightly ordered the code to make it clearer what is connected with what. Scheme:

     CREATE TABLE main ( id INT NOT NULL, PRIMARY KEY (id), name TEXT ) ENGINE=INNODB; CREATE TABLE sub1 ( id INT NOT NULL, id_main INT, note text, PRIMARY KEY (id), FOREIGN KEY (id_main) REFERENCES main(id) ) ENGINE=INNODB; CREATE TABLE sub2 ( id int NOT NULL, id_main INT, txt text, PRIMARY KEY (id), FOREIGN KEY (id_main) REFERENCES main(id) ) ENGINE=INNODB; INSERT INTO main (id,name) VALUES (1,'var1'), (2,'var2'); INSERT INTO sub1 (id,id_main,note) VALUES (1,1,'asd'), (2,1,'asd'), (3,2,'asd'); INSERT INTO sub2 (id,id_main,txt) VALUES (3,1,'asd'), (4,1,'asd'), (5,2,'asd');

    Actually request:

     SELECT m.id, m.name, s1.s1id, s1.note, s2.s2id, s2.txt FROM main m, (SELECT max(id) as s1id, id_main, note FROM sub1 GROUP BY id_main) AS s1, (SELECT max(id) as s2id, id_main, txt FROM sub2 GROUP BY id_main) AS s2 WHERE s1.id_main = m.id AND s2.id_main = m.id

    Demo (sqlfiddle.com)

      Can. Drip in the direction of join, group. max and where. Maybe another union.

      You need to select all the records of the first table, make a join with the second and third, group by id2 or id3 (and the remaining non-aggregated fields) fields (and maybe together, I think when you figure it out) and use the max function.

      You can add more subqueries :-)

      Documentation on the use of the expression select can be found here

      • Why, I experimented with both group and c max and joins ... But it’s impossible to do something like this: (... There was a thought to somehow set the MAX condition like HAVING MAX () but it doesn’t work ... - Nikki
      • four
        And you describe exactly what you did, and how it does not work. In principle, I will not write a ready-made answer, without being able to evaluate your torment and suffering :-D A scheme of how you can get the desired result by applying the knowledge from the MySql documentation, I described in the answer. - Chad
      • I decided to split the request into 3 pieces. so it turns out faster. - Nikki
      • one
        @Nikki: get faster runtime, or faster to write code? Will you give us a slow version without a breakdown of the request? - VladD
      • Left Join and HAVING to help you (: - Ilyas