Mysql – greatest n per group with a 3 tables join

greatest-n-per-groupjoin;mariadbmariadb-10.1MySQL

Working on a project with following objects:

  • People

    • id

  • Vehicles

    • id
    • model: text

and a link table for owners which passes through owners changes

  • Owner_Changes
    • old_person_id
    • new_person_id
    • vehicle_id
    • date

I'm trying to get a vehicle owner (People->OwnerChanges->Vehicles, and the only way to achieve that is to get the latest owner change by date.

I'm trying to join People/OwnerChanges/Vehicles with following query and get always the old vehicles associated with people

SELECT  `people`.`id`, `people`.`name`, `people`.`surname`, `people`.`email`,
        `people`.`city`, `people`.`receives_news` , `vehicles`.`model`,
        `vehicles`.`id`, `owner_changes`.`date`
    FROM  `bm_prod`.`people`
    INNER JOIN  
        ( SELECT  vehicle_id, new_person_id, date, MAX(date) maxDate
            FROM  owner_changes
            GROUP BY  id 
        ) owner_changes  ON `owner_changes`.`new_person_id` = `people`.`id`
      AND  `owner_changes`.`date` = `owner_changes`.`maxDate`
    INNER JOIN  `bm_prod`.`vehicles`  ON (`owner_changes`.`vehicle_id`
                                                    = `vehicles`.`id`)
    WHERE  `receives_news` = '1'
      AND  NOT (`email` = '')
      AND  `people`.`receives_news` = '1'
      AND  LOWER(`vehicles`.`model`) LIKE '%mts%'
    ORDER BY  `vehicles`.`id` ASC, `owner_changes`.`date` DESC
    LIMIT  200

Ids 23 and 111 are examples of duplicates I'd like to choose the latest by date (DESC):
enter image description here

Any help or documentation about greatest-n-per-group with 3 tables will be most appreciated.

Create table statement is following:

CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`surname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`birthdate` date DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`fax_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`mobile_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'Mobile Number',
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`street_number` varchar(255) DEFAULT NULL,
`zipcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`city` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`country` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`company` varchar(255) NOT NULL COMMENT 'Company working in',
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'a comment on person',
`receives_news` tinyint(1) NOT NULL DEFAULT '1',
`enumerate_id` int(11) NOT NULL COMMENT 'person_category_id',
`driving_license_type_enumerate_id` int(11) DEFAULT NULL COMMENT 'enumerate_id',
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3299 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC


CREATE TABLE `vehicles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`provider_id` int(11) NOT NULL,
`model` varchar(255) COLLATE utf8_bin NOT NULL,
`displacement` int(11) DEFAULT NULL COMMENT 'Cylindrée',
`type_reception` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Reception_par_type',
`km` bigint(20) DEFAULT NULL,
`first_use` date DEFAULT NULL,
`color` varchar(255) COLLATE utf8_bin NOT NULL,
`enumerate_id` int(11) DEFAULT NULL COMMENT 'reference to enumerate->vehicle_usage_type',
`chassis_number` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Numéro de chassis',
`plate_number` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Numéro de plaque',
`vehicle_type_id` int(11) NOT NULL,
`warranty` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Warranty',
`production_year` int(11) DEFAULT NULL,
`comment` mediumtext COLLATE utf8_bin,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1792 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `owner_changes` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'identifier',
`vehicle_id` int(11) NOT NULL,
`new_person_id` int(11) DEFAULT NULL,
`old_person_id` int(11) DEFAULT NULL,
`buy_price` float DEFAULT NULL,
`sell_price` float DEFAULT NULL,
`date` date DEFAULT NULL,
`km` bigint(20) DEFAULT NULL,
`comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2336 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Version: MariaDB 10.1:

pg@vmDebDev1: ~$ mysql –version mysql Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Best Answer

Any help or documentation about greatest-n-per-group with 3 tables will be most appreciated.

Source data tables:

create table people (id int, name text) 
select 1 id, 'John' name union all
select 2   , 'Mark'      union all
select 3   , 'Igor';

create table vehicles (id int, model text) 
select 11 id,'digger' model union all
select 12   ,'crane';

create table ownerchanges (old_person_id int, new_person_id int, vehicle_id int, cdate date)
select null old_person_id, 1 new_person_id, 11 vehicle_id, '2018-01-01' cdate union all
select    1              , 3              , 11           , '2018-01-02'       union all
select    3              , 2              , 11           , '2018-01-03'       union all
select null              , 1              , 12           , '2018-01-01'       union all
select    1              , 3              , 12           , '2018-01-02' ;

Query:

select v.id, v.model, p.id, p.name
from vehicles v, 
     people p,
     ownerchanges o,
     (select oc.vehicle_id, max(oc.cdate) maxdate -- select max date
      from ownerchanges oc                        -- of owner change
      group by oc.vehicle_id) last                -- for each vehicle
where o.vehicle_id = last.vehicle_id   -- use it to select
  and o.cdate = last.maxdate           -- proper change record
  and o.vehicle_id = v.id     -- and attach data
  and o.new_person_id = p.id; -- we need

fiddle

UPDATE WITH JOINS

Please write with JOIN...ON instead of commajoin. – Rick James

This, of course, will reduce visibility, but if you want...

select v.id, v.model, p.id, p.name
from ownerchanges o
join people p on o.new_person_id = p.id
join vehicles v on o.vehicle_id = v.id
join (select oc.vehicle_id, max(oc.cdate) maxdate
      from ownerchanges oc                       
      group by oc.vehicle_id) last  on o.vehicle_id = last.vehicle_id 
                                   and o.cdate = last.maxdate
Related Question