MySQL JOIN the most recent row only?

Question

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)

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
   )

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

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.

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;
    }
}