datether datether - 6 months ago 9
MySQL Question

Show data from last updated entry in table 1, joined with information from table2

I have two tables:

rankHistory which stores the rank of a bookID against it's given keyword.
It's structure is as following:

create table rankHistory (
id int(10),
rankUpdated timestamp,
bookId varchar(10),
keyword varchar(100),
rank int(5)
);


Example data contained within this table :

INSERT INTO rankHistory
(`id`, `rankUpdated`,`bookId`,`keyword`,`rank`)
VALUES
(1, '2016-05-27 05:11:44', 'BK-001','romantic','80'),
(2, '2016-05-28 08:11:44', 'BK-001','romantic','15'),
(3, '2016-05-28 09:11:44', 'BK-001','romantic','13'),
(4, '2016-05-27 07:11:44', 'BK-001','romantic book','77'),
(5, '2016-05-28 08:11:44', 'BK-001','romantic book','25'),
(6, '2016-05-28 09:11:44', 'BK-001','romantic book','22'),
(7, '2016-05-27 05:11:44', 'BK-002','horror','65'),
(8, '2016-05-28 08:11:44', 'BK-002','horror','10'),
(9, '2016-05-29 07:11:44', 'BK-002','horror book','25'),
(10, '2016-05-29 08:11:44', 'BK-002','horror book','9')
;


bookConfig contains information of the bookID with it's given keyword.
It's structure is as following:

create table bookConfig (
id int(10),
category varchar(40),
author varchar(255),
info varchar (255)
);


Example data contained within this table :

INSERT INTO bookConfig
(`id`,`bookId`,`keyword`, `category`, `author`,`info`)
VALUES
(1, 'BK-001', 'romantic', 'Romance', 'Author1','Romance themed book written by author1'),
(2, 'BK-001', 'romantic book', 'Romance', 'Author1','Romance themed book written by author1'),
(3, 'BK-002', 'horror ', 'Horror', 'Author2','Horror themed book written by author2'),
(4, 'BK-002', 'horror book', 'Horror', 'Author2','Horror book written by author2')
;


I have consolidated this in http://sqlfiddle.com/#!9/9502a9.

The table rankHistory contains tens of thousands records similar as provided data and my concern is also with speed. The structure is probably wrong from the beginning but it's a legacy I have to work with unfortunately.

What I would like to achieve is the following :

Select the entry with the latest timestamp for each unique bookid/keyword combination and then join the information related to that combination contained in bookConfig.

So the desired output would be :

rankUpdated bookId keyword rank,category,author, info
----------------------------------------------------------------------------
2016-05-28 09:11:44, BK-001, romantic, 13, romance, Author1, Romance themed book..
2016-05-28 09:11:44, BK-001, romantic book, 22, romance, Author1, Romance themed book..
2016-05-28 08:11:44, BK-002, horror, 10, horror, Author2, Horror themed book..
2016-05-29 08:11:44, BK-002, horror book, 9, horror, Author2, Horror book ...


I have tried several ways to accomplish this without success and am lost of how to achieve this properly. I would be very thankful if someone more versed in mySQL can point me out the best way to achieve this .

Many thanks in advance for sharing your expertise

Answer

One option is to do a series of joins to achieve what you want:

SELECT COALESCE(t2.rankUpdated, 'NA'), AS rankUpdated, t1.bookId, t1.keyword,
    COALESCE(t2.rank, 'NA') AS rank, t1.category, t1.author, t1.info
FROM bookConfig t1
LEFT JOIN
(
    SELECT r1.bookId, r1.rankUpdated, r1.rank, r1.keyword
    FROM rankHistory r1
    INNER JOIN
    (
        SELECT bookId, keyword, MAX(rankUpdated) AS rankUpdated
        FROM rankHistory
        GROUP BY bookId, keyword
    ) r2
        ON r1.bookId = r2.bookId AND r1.keyword = r2.keyword AND
           r1.rankUpdated = r2.rankUpdated
) t2
    ON t1.bookId = t2.bookId AND t1.keyword = t2.keyword

Follow the link below for a running demo:

SQLFiddle

Comments