MySQL JOIN the most recent row only

Question

I have a table customer that stores a customer id  email and reference  There is an additional table customer data that stores a historical record of the changes made to the customer  i e  when there s a change made a new row is inserted   In order to display the customer information in a table  the two tables need to be joined  however only the most recent row from customer data should be joined to the customer table   It gets a little more complicated in that the query is paginated  so has a limit and an offset   How can I do this with MySQL  I think I m wanting to put a DISTINCT in there somewhere     The query at the minute is like this-  SELECT    CONCAT title     forename     surname  AS name FROM customer c INNER JOIN customer data d on c customer id d customer id WHERE name LIKE   Smith   LIMIT 10  20   Additionaly  am I right in thinking I can use CONCAT with LIKE in this way    I appreciate that INNER JOIN might be the wrong type of JOIN to use  I actually have no clue what the difference is between the different JOINs  I m going to look into that now

User · Answer

You can also do this  SELECT    CONCAT title       forename       surname  AS name FROM      customer c LEFT JOIN                  SELECT   FROM  customer data ORDER BY id DESC             customer data ON  customer data customer id   c customer id  GROUP BY  c customer id           WHERE     CONCAT title       forename       surname  LIKE   Smith    LIMIT     10  20

User · Answer

SELECT CONCAT title     forename     surname  AS name   FROM customer c  INNER JOIN customer data d on c id d customer id WHERE name LIKE   Smith      i think you need to change  c customer id to c id  else update table structure

User · Answer

You may want to try the following   SELECT    CONCAT title       forename       surname  AS name FROM      customer c JOIN                      SELECT    MAX id  max id  customer id                FROM      customer data                GROUP BY  customer id             c max ON  c max customer id   c customer id  JOIN      customer data cd ON  cd id   c max max id  WHERE     CONCAT title       forename       surname  LIKE   Smith    LIMIT     10  20    Note that a JOIN is just a synonym for INNER JOIN   Test case   CREATE TABLE customer  customer id int   CREATE TABLE customer data      id int      customer id int      title varchar 10      forename varchar 10      surname varchar 10      INSERT INTO customer VALUES  1   INSERT INTO customer VALUES  2   INSERT INTO customer VALUES  3    INSERT INTO customer data VALUES  1  1   Mr    Bobby    Smith    INSERT INTO customer data VALUES  2  1   Mr    Bob    Smith    INSERT INTO customer data VALUES  3  2   Mr    Jane    Green    INSERT INTO customer data VALUES  4  2   Miss    Jane    Green    INSERT INTO customer data VALUES  5  3   Dr    Jack    Black      Result  query without the LIMIT and WHERE    SELECT    CONCAT title       forename       surname  AS name FROM      customer c JOIN                      SELECT    MAX id  max id  customer id                FROM      customer data                GROUP BY  customer id             c max ON  c max customer id   c customer id  JOIN      customer data cd ON  cd id   c max max id     -----------------    name               -----------------    Mr Bob Smith        Miss Jane Green     Dr Jack Black      -----------------  3 rows in set  0 00 sec

User · Answer

It s a good idea that logging actual data into  customer data  table  With this data you can select all data from  customer data  table as you wish

User · Answer

I know this question is old  but it s got a lot of attention over the years and I think it s missing a concept which may help someone in a similar case  I m adding it here for completeness sake   If you cannot modify your original database schema  then a lot of good answers have been provided and solve the problem just fine   If you can  however  modify your schema  I would advise to add a field in your customer table that holds the id of the latest customer data record for this customer     CREATE TABLE customer     id INT UNSIGNED NOT NULL AUTO INCREMENT PRIMARY KEY    current data id INT UNSIGNED NULL DEFAULT NULL     CREATE TABLE customer data      id INT UNSIGNED NOT NULL AUTO INCREMENT PRIMARY KEY     customer id INT UNSIGNED NOT NULL      title VARCHAR 10  NOT NULL     forename VARCHAR 10  NOT NULL     surname VARCHAR 10  NOT NULL      Querying customers  Querying is as easy and fast as it can be   SELECT c    d title  d forename  d surname FROM customer c INNER JOIN customer data d on d id   c current data id WHERE        The drawback is the extra complexity when creating or updating a customer   Updating a customer  Whenever you want to update a customer  you insert a new record in the customer data table  and update the customer record   INSERT INTO customer data  customer id  title  forename  surname  VALUES 2   Mr    John    Smith    UPDATE customer SET current data id   LAST INSERT ID   WHERE id   2    Creating a customer  Creating a customer is just a matter of inserting the customer entry  then running the same statements   INSERT INTO customer    VALUES      SET  customer id   LAST INSERT ID    INSERT INTO customer data  customer id  title  forename  surname  VALUES  customer id   Mr    John    Smith    UPDATE customer SET current data id   LAST INSERT ID   WHERE id    customer id    Wrapping up  The extra complexity for creating updating a customer might be fearsome  but it can easily be automated with triggers   Finally  if you re using an ORM  this can be really easy to manage  The ORM can take care of inserting the values  updating the ids  and joining the two tables automatically for you   Here is how your mutable Customer model would look like   class Customer       private int id      private CustomerData currentData       public Customer String title  String forename  String surname                this update title  forename  surname              public void update String title  String forename  String surname                this currentData   new CustomerData this  title  forename  surname              public String getTitle                 return this currentData getTitle               public String getForename                 return this currentData getForename               public String getSurname                 return this currentData getSurname              And your immutable CustomerData model  that contains only getters   class CustomerData       private int id      private Customer customer      private String title      private String forename      private String surname       public CustomerData Customer customer  String title  String forename  String surname                this customer   customer          this title      title          this forename   forename          this surname    surname             public String getTitle                 return this title             public String getForename                 return this forename             public String getSurname                 return this surname

User · Answer

For anyone who must work with an older version of MySQL  pre-5 0 ish  you are unable to do sub-queries for this type of query  Here is the solution I was able to do and it seemed to work great   SELECT MAX d id   d2    CONCAT title     forename     surname  AS name FROM customer AS c  LEFT JOIN customer data as d ON c customer id d customer id  LEFT JOIN customer data as d2 ON d id d2 id WHERE CONCAT title       forename       surname  LIKE   Smith   GROUP BY c customer id LIMIT 10  20    Essentially this is finding the max id of your data table joining it to the customer then joining the data table to the max id found  The reason for this is because selecting the max of a group doesn t guarantee that the rest of the data matches with the id unless you join it back onto itself   I haven t tested this on newer versions of MySQL but it works on 4 0 30

User · Answer

If you are working with heavy queries  you better move the request for the latest row in the where clause  It is a lot faster and looks cleaner   SELECT c    FROM client AS c LEFT JOIN client calling history AS cch ON cch client id   c client id WHERE    cch cchid           SELECT MAX cchid        FROM client calling history       WHERE client id   c client id AND cal event id   c cal event id

User · Answer

Presuming the autoincrement column in customer data is named Id  you can do   SELECT CONCAT title     forename     surname  AS name   FROM customer c     INNER JOIN customer data d          ON c customer id d customer id WHERE name LIKE   Smith       AND d ID                     Select Max D2 Id                  From customer data As D2                 Where D2 customer id   D customer id                   LIMIT 10  20

[mysql] MySQL JOIN the most recent row only?

Examples related to mysql

Examples related to sql

Examples related to join