LEFT JOIN select latest row from the left join

I have a company database that has all its details within it. It also has a separate table where credit ratings are stored.

DROP TABLE IF EXISTS `company`;
CREATE TABLE IF NOT EXISTS `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `email` varchar(255) CHARACTER SET latin1 NOT NULL,
  `address` varchar(255) NOT NULL,
  `address2` text NOT NULL,
  `address3` text NOT NULL,
  `phone` text NOT NULL,
  `contacts` text NOT NULL,
  `islive` tinyint(1) NOT NULL DEFAULT '1',
  `qt` tinyint(1) NOT NULL DEFAULT '30',
  `pt` tinyint(1) NOT NULL DEFAULT '30',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `creditrating`;
CREATE TABLE IF NOT EXISTS `creditrating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `rating` int(11) NOT NULL,
  `arating` varchar(10) NOT NULL,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `thedate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `company` (`id`, `name`, `email`, `address`, `address2`, `address3`, `phone`, `contacts`, `islive`, `qt`, `pt`) VALUES
(190, 'Test Company', '[email protected]', 'Testing Client 1rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 1rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 1rnsdfsdfsdfrnsdfsdfsdf', '65165156', 'test name', 1, 30, 30),
(191, 'Test Company 2', '[email protected]', 'Testing Client 2rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 2rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 2rnsdfsdfsdfrnsdfsdfsdf', '65165156', 'test name 2', 1, 30, 30);

INSERT INTO `creditrating` (`id`, `cid`, `rating`, `arating`, `type`, `thedate`) VALUES
(3, 190, 684, 'da774', 1, '2021-03-30 15:08:52'),
(6, 190, 222, 'DD222', 1, '2021-03-30 17:46:22');

I am trying to only retrieve the company details and only the latest row on the credit rating table.

The SQL I have got closest to getting this to work is (Mysql 5.6):

SELECT c.id
     , AES_DECRYPT(c.name, 'co1') as name
     , AES_DECRYPT(c.phone, 'co3') as phone
     , c.islive
     , r.rating 
  FROM company c 
  LEFT 
  JOIN creditrating r 
    ON (SELECT r.thedate FROM creditrating WHERE c.id=r.cid ORDER BY r.thedate DESC LIMIT 1)

DB FIDDLE HERE

At the moment it is bringing back 2 rows of the same company instead of just 1.

Thank you in advance for any pointers you guys can through my way.

Answer

prior versions don’t have window functions but you can switch to user defined variables

DROP TABLE IF EXISTS `company`;
CREATE TABLE IF NOT EXISTS `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `email` varchar(255) CHARACTER SET latin1 NOT NULL,
  `address` varchar(255) NOT NULL,
  `address2` text NOT NULL,
  `address3` text NOT NULL,
  `phone` text NOT NULL,
  `contacts` text NOT NULL,
  `islive` tinyint(1) NOT NULL DEFAULT '1',
  `qt` tinyint(1) NOT NULL DEFAULT '30',
  `pt` tinyint(1) NOT NULL DEFAULT '30',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `creditrating`;
CREATE TABLE IF NOT EXISTS `creditrating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `rating` int(11) NOT NULL,
  `arating` varchar(10) NOT NULL,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `thedate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `company` (`id`, `name`, `email`, `address`, `address2`, `address3`, `phone`, `contacts`, `islive`, `qt`, `pt`) VALUES
(190, 'Test Company', '[email protected]', 'Testing Client 1rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 1rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 1rnsdfsdfsdfrnsdfsdfsdf', '65165156', 'test name', 1, 30, 30),
(191, 'Test Company 2', '[email protected]', 'Testing Client 2rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 2rnsdfsdfsdfrnsdfsdfsdf', 'Testing Client 2rnsdfsdfsdfrnsdfsdfsdf', '65165156', 'test name 2', 1, 30, 30);

INSERT INTO `creditrating` (`id`, `cid`, `rating`, `arating`, `type`, `thedate`) VALUES
(3, 190, 684, 'da774', 1, '2021-03-30 15:08:52'),
(6, 190, 222, 'DD222', 1, '2021-03-30 17:46:22');
SELECT 
    id, name, phone, islive, rating, `thedate`
FROM
    (SELECT 
        name,
            phone,
            islive,
            rating,
            `thedate`,
            IF(id = @id, @rnk:[email protected] + 1, @rnk:=1) AS rnk,
            @id:=id AS id
    FROM
        (SELECT 
        c.id,
            AES_DECRYPT(c.name, 'co1') AS name,
            AES_DECRYPT(c.phone, 'co3') AS phone,
            c.islive,
            r.rating,
            `thedate`
    FROM
        company c
    LEFT JOIN creditrating r ON c.id = r.cid) t1, (SELECT @id:=- 1, @rnk:=0) t2
    ORDER BY id , `thedate` DESC) t3
WHERE
    rnk = 1;
 id | name | phone | islive | rating | thedate            
--: | :--- | :---- | -----: | -----: | :------------------
190 | null | null  |      1 |    222 | 2021-03-30 17:46:22
191 | null | null  |      1 |   null | null               

db<>fiddle here