LIMIT 1 with LEFT JOIN

Question

Good to start I will post the SQL I rode here:

    SELECT
    a.*,
    b.name AS currentClubName,
    c.name AS transferorClubName,
    d.name AS contactName,
    d.phone AS contactPhone,
    d.phoneApp AS contactPhoneApp,
    d.email AS contactEmail,
    d.note AS contactNote,
    e.endDate
FROM
    athlete a
        LEFT JOIN clubs b ON a.currentClubCode = b.code
        LEFT JOIN clubs c ON a.transferorClubCode = c.code
        LEFT JOIN contacts d ON a.contactCode = d.code
        LEFT JOIN negotiation e ON e.athleteCode = a.code
ORDER BY
    a.name;

Keeping in mind that the table athlete has only one a.currentClubCode,a.transferorClubCode and a a.contactCode, the answer will not suffer duplicity.

But in the case of the table negotiation are recorded several negotiations for an athlete and I need to recover only the e.endDate of the longest date, that is to say only a result.

In the current way I am having a duplicity, if a player has 3 record in the table negotiation he is listed 3 times in select.

How to solve this? I tried to use the LEFT JOIN as there may be no record of trading and therefore field e.endDate must be null.

Note: I am using version 8 of mysql.

Follows the structure of the table negotiation:

CREATE TABLE IF NOT EXISTS `negotiation` (
  `code` INT AUTO_INCREMENT,
  `athleteCode` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  `endDate` date NOT NULL,
  `note` text NOT NULL,
  `link` text DEFAULT NULL,
  PRIMARY KEY (`code`),
  FOREIGN KEY (athleteCode) REFERENCES athlete(code) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

__________ Edit _____________

I got the result I wanted that way, but I don’t know if it’s the best way to do it:

    SELECT
    a.*,
    b.name AS currentClubName,
    c.name AS transferorClubName,
    d.name AS contactName,
    d.phone AS contactPhone,
    d.phoneApp AS contactPhoneApp,
    d.email AS contactEmail,
    d.note AS contactNote,
   (SELECT endDate FROM negotiation WHERE athleteCode = a.code ORDER BY endDate DESC LIMIT 1) AS endDate
FROM
    athlete a
        LEFT JOIN clubs b ON a.currentClubCode = b.code
        LEFT JOIN clubs c ON a.transferorClubCode = c.code
        LEFT JOIN contacts d ON a.contactCode = d.code
ORDER BY
    a.name;

Answer 1

I believe the following command elucidates the problem. I did not use Limit 1. I used what is most common in these cases, which is to use a sub-consumption to restrict the result as mentioned in the @Scalabrini.

Select distinct
    a.*,
    b.name AS currentClubName,
    c.name AS transferorClubName,
    d.name AS contactName,
    d.phone AS contactPhone,
    d.phoneApp AS contactPhoneApp,
    d.email AS contactEmail,
    d.note AS contactNote,
    e.endDate
From athlete a
LEFT JOIN clubs b ON a.currentClubCode = b.code
LEFT JOIN clubs c ON a.transferorClubCode = c.code
LEFT JOIN contacts d ON a.contactCode = d.code
LEFT JOIN negotiation e ON a.code = e.athleteCode 
                       and e.endDate = (select max(endDate) 
                                          from negotiation e2
                                         where e2.athleteCode = a.code)
ORDER BY
    a.name;

Some remarks:

1. Included the distinct to suppress possible duplicates of the result.
2. In the SQL section that you have assembled, it is LEFT JOIN negotiation e ON e.athleteCode = a.code. Inverti to LEFT JOIN negotiation e ON a.code = e.athleteCode since in the Left Join will return the relationship records mentioned on the left (LEFT).
3. To make the junction Join let’s restrict to select only the highest date record max(endDate) for that particular athlete according to:

LEFT JOIN negotiation e ON a.code = e.athleteCode 
                       and e.endDate = (select max(endDate) 
                                          from negotiation e2
                                         where e2.athleteCode = a.code)

Distinct and Datetime:

It is worth mentioning that the need to use the distinct in this case it is the reason of the field endDate be the type date, 'cause if you were the type datetime, probably there would be no duplicity in the result, especially if using the datetime with millisecond accuracy.

To learn more about the datetime in Mysql you can consult : https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

Answer 2

Then just sort the data by DESC endDate and limit the result to 1.

SELECT
    a.*,
    b.name AS currentClubName,
    c.name AS transferorClubName,
    d.name AS contactName,
    d.phone AS contactPhone,
    d.phoneApp AS contactPhoneApp,
    d.email AS contactEmail,
    d.note AS contactNote,
    e.endDate
FROM
    athlete a
        LEFT JOIN clubs b ON a.currentClubCode = b.code
        LEFT JOIN clubs c ON a.transferorClubCode = c.code
        LEFT JOIN contacts d ON a.contactCode = d.code
        LEFT JOIN negotiation e ON e.athleteCode = a.code
ORDER BY
    a.name, e.endDate desc
LIMIT 1;