SELECT * FROM (
                  SELECT d1.id,
                         `bt`.`name`,
                         `r`.`alias`        AS `rooms`,
                         `d1`.`deals_bank_id`,
                         `ot`.`alias`       AS `object_type`,
                         `d1`.`reg_date`,
                         `pbo`.`square`,
                         `pdb`.`deals_buyer_type_id`,
                         `d1`.`agreement_date`,
                         `con`.`concession` AS `concession`,
                         `d1`.`deals_concession_id`,
                         `d1`.`deals_seller_id`,
                         `d1`.`wholesale`,
                         `hc`.`name`        AS `hc_name`,
                         `loc`.`address`    AS `loc_address`,
                         d1.pbo_id
                  FROM (
                           SELECT d.id,
                                  `d`.`deals_object_id`,
                                  `d`.`agreement_date`,
                                  `d`.`reg_date`,
                                  `d`.`document_date`,
                                  `d`.`registration_number`,
                                  `d`.`mortgage_term`,
                                  `d`.`mortgage_number`,
                                  `d`.`deals_concession_id`,
                                  `d`.`deals_buyer_id`,
                                  `d`.`deals_bank_id`,
                                  `d`.`deals_seller_id`,
                                  `d`.`wholesale`,
                                  `d`.`price_square_r`,
                                  `d`.`method_ppsmr_id`,
                                  `d`.`compensation_amount`,
                                  `d`.`ddu_amount`,
                                  pbo.id as pbo_id
                           from parser_deals d
                                    inner join parser_deals_objects pdo on d.deals_object_id = pdo.id
                                    inner join parser_building_objects pbo on pdo.building_object_id = pbo.id
                                    inner join parser_building pb on pbo.building_id = pb.id
                           where pb.housing_complex_id = 75
                             and d.agreement_date <= '2019-10-21'
                           GROUP BY pbo.id, d.agreement_date
                       ) `d1`
                           JOIN
                       (
                           SELECT d.id, MAX(d.agreement_date) as agr_date, d.deals_object_id
                           from parser_deals d
                                    inner join parser_deals_objects pdo on d.deals_object_id = pdo.id
                                    inner join parser_building_objects pbo on pdo.building_object_id = pbo.id
                                    inner join parser_building pb on pbo.building_id = pb.id
                           where pb.housing_complex_id = 75
                             and d.agreement_date <= '2019-10-21'
                           GROUP BY pbo.id
                       ) `d2` ON
                                   d1.agreement_date = d2.agr_date
                               AND d1.deals_object_id = d2.deals_object_id

                           LEFT JOIN parser_deals_objects pdo
                                     ON d1.deals_object_id = pdo.id
                           LEFT JOIN parser_building_objects pbo
                                     on pdo.building_object_id = pbo.id
                           LEFT JOIN `parser_deals_concession` `con`
                                     ON con.id = d1.deals_concession_id
                           LEFT JOIN `parser_object_type` `ot`
                                     ON ot.id = pbo.object_type_id
                           LEFT JOIN `parser_deals_buyers` `pdb`
                                     ON pdb.id = d1.deals_buyer_id
                           LEFT JOIN `parser_deals_buyer_type` `bt`
                                     ON bt.id = pdb.deals_buyer_type_id
                           LEFT JOIN `parser_rooms` `r`
                                     ON r.id = pbo.custom_rooms_id
                           LEFT JOIN `parser_deals_banks` `bank`
                                     ON bank.id = d1.deals_bank_id
                           LEFT JOIN `parser_building` `b`
                                     ON b.id = pbo.building_id
                           LEFT JOIN `parser_housing_complex` `hc`
                                     ON hc.id = b.housing_complex_id
                           LEFT JOIN `core_location` `loc`
                                     ON loc.id = b.location_id
                           LEFT JOIN `parser_region` `region`
                                     ON region.id = b.region_id
                           LEFT JOIN `parser_objectclass` `class`
                                     ON class.id = b.objectclass_id
                           LEFT JOIN `parser_developer` `dev`
                                     ON dev.id = b.developer_id
                           LEFT JOIN `parser_builder` `br`
                                     ON br.id = b.builders_id
                           LEFT JOIN `parser_deals_seller` `ds`
                                     ON ds.id = d1.deals_seller_id
                           LEFT JOIN `parser_method_ppsmr` `ppsmr`
                                     ON ppsmr.id = d1.method_ppsmr_id
                  where `ot`.`alias` IN ('kvartira', 'apartamenty')
                    AND NOT (`d1`.`deals_concession_id` = '2')
                    AND not (`d1`.`deals_seller_id` = '2')
                  ) d
LEFT JOIN (SELECT
                  d.id,
               `bt`.`name`,
               `r`.`alias` AS `rooms`,
               `d`.`deals_bank_id`,
               `ot`.`alias` AS `object_type`,
               `d`.`reg_date`,
               `pbo`.`square`,
               `pdb`.`deals_buyer_type_id`,
               `d`.`agreement_date`,
               `con`.`concession` AS `concession`,
               `d`.`deals_concession_id`,
               `d`.`deals_seller_id`,
               `d`.`wholesale`,
               `hc`.`name` AS `hc_name`,
               `loc`.`address` AS `loc_address`,
                  pbo.id as pbo_id
           FROM
               `parser_building_objects` `pbo`
                   INNER JOIN `parser_deals_objects` `do`
                              ON do.building_object_id = pbo.id
                   INNER JOIN
               (SELECT
                       d1.id,
                    `d1`.`deals_object_id`,
                    `d1`.`agreement_date`,
                    `d1`.`reg_date`,
                    `d1`.`document_date`,
                    `d1`.`registration_number`,
                    `d1`.`mortgage_term`,
                    `d1`.`mortgage_number`,
                    `d1`.`deals_concession_id`,
                    `d1`.`deals_buyer_id`,
                    `d1`.`deals_bank_id`,
                    `d1`.`deals_seller_id`,
                    `d1`.`wholesale`,
                    `d1`.`price_square_r`,
                    `d1`.`method_ppsmr_id`,
                    `d1`.`compensation_amount`,
                    `d1`.`ddu_amount`
                FROM
                    `parser_deals` `d1`
                        INNER JOIN `parser_deals_objects` `do1`
                                   ON do1.id = d1.deals_object_id
                        LEFT JOIN
                    (SELECT
                         `do2`.`building_object_id`,
                         `d3`.*
                     FROM
                         `parser_deals` `d3`
                             INNER JOIN `parser_deals_objects` `do2`
                                        ON do2.id = d3.deals_object_id
                    ) `d2`
                    ON (
                               do1.building_object_id = d2.building_object_id
                           )
                        AND (d1.id <> d2.id)
                        AND (
                               d1.agreement_date < d2.agreement_date
                           )
                        AND (
                               d2.agreement_date <= '2019-10-21'
                           )
                WHERE d2.id IS NULL) `d`
               ON d.deals_object_id = do.id
                   LEFT JOIN `parser_deals_concession` `con`
                             ON con.id = d.deals_concession_id
                   LEFT JOIN `parser_object_type` `ot`
                             ON ot.id = pbo.object_type_id
                   LEFT JOIN `parser_deals_buyers` `pdb`
                             ON pdb.id = d.deals_buyer_id
                   LEFT JOIN `parser_deals_buyer_type` `bt`
                             ON bt.id = pdb.deals_buyer_type_id
                   LEFT JOIN `parser_rooms` `r`
                             ON r.id = pbo.custom_rooms_id
                   LEFT JOIN `parser_deals_banks` `bank`
                             ON bank.id = d.deals_bank_id
                   LEFT JOIN `parser_building` `b`
                             ON b.id = pbo.building_id
                   LEFT JOIN `parser_housing_complex` `hc`
                             ON hc.id = b.housing_complex_id
                   LEFT JOIN `core_location` `loc`
                             ON loc.id = b.location_id
                   LEFT JOIN `parser_region` `region`
                             ON region.id = b.region_id
                   LEFT JOIN `parser_objectclass` `class`
                             ON class.id = b.objectclass_id
                   LEFT JOIN `parser_developer` `dev`
                             ON dev.id = b.developer_id
                   LEFT JOIN `parser_builder` `br`
                             ON br.id = b.builders_id
                   LEFT JOIN `parser_deals_seller` `ds`
                             ON ds.id = d.deals_seller_id
                   LEFT JOIN `parser_method_ppsmr` `ppsmr`
                             ON ppsmr.id = d.method_ppsmr_id
           WHERE (
                   `d`.`agreement_date` <= '2019-10-21'
               )
             AND (`b`.`housing_complex_id` = 75)
             AND (
                   `d`.`agreement_date` <= '2019-10-21'
               )
             AND (
                   `ot`.`alias` IN ('kvartira', 'apartamenty')
               )
           GROUP BY `pbo`.`id`
           HAVING NOT (
                   (`d`.`deals_concession_id` = '2')
                   AND (`d`.`deals_seller_id` = '2')
               )
           ORDER BY `d`.`agreement_date`) d1
ON d.pbo_id = d1.pbo_id