1

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

  • You use FROM owner_changes GROUP BY id in subquery - but there is no id field in the OwnerChanges' structure. – Akina Jul 4 '18 at 4:58
  • Please provide SHOW CREATE TABLE. – Rick James Jul 4 '18 at 13:02
  • @RickJames done – Philippe Gachoud Jul 4 '18 at 15:29
1

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
  • 1
    Please write with JOIN...ON instead of commajoin. – Rick James Jul 4 '18 at 12:56
  • Many thx for the spent time @RickJames @Akina ! seems that the last keyword is not recognised by mysql, is it a SQL server statement? Unrecognized keyword. (near "last" at position 272) – Philippe Gachoud Jul 4 '18 at 15:45
  • @PhilippeGachoud you did something wrong. last is just an alias for the derived table. – ypercubeᵀᴹ Jul 4 '18 at 16:50
  • @ypercubeᵀᴹ @Akina last seems to be a keyword of mysql, had to change it with last1 – Philippe Gachoud Jul 4 '18 at 17:22
  • What version do you use? LAST is not a reserved keyword, not even in version 8: dev.mysql.com/doc/refman/8.0/en/… – ypercubeᵀᴹ Jul 4 '18 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.