Instead of a correlated subquery, you could also use a single-pass derived table:
1) Gets client names and data of oldest linked record:
SELECT
client.Name
, client.Surname
, client_data.Data
FROM table2 as client
INNER JOIN table1 as client_data1
INNER JOIN (
SELECT ClientID, MIN(Number) as Number
FROM table1 AS client_data
GROUP BY ClientID
) as client_data2
ON client_data1.ClientID = client_data2.ClientID
AND client_data1.Number = client_data2.Number;
2) Gets client names and data of newest linked record:
SELECT
client.Name
, client.Surname
, client_data1.Data
FROM table2 as client
INNER JOIN table1 as client_data1
INNER JOIN (
SELECT ClientID, MAX(Number) as Number
FROM table1 AS client_data
GROUP BY ClientID
) as client_data2
ON client_data1.ClientID = client_data2.ClientID
AND client_data1.Number = client_data2.Number;
3) Gets client names and data of linked record with greatest time gap:
SELECT
client.Name
, client.Surname
, client_data.Data
FROM table2 as client
INNER JOIN table1 as client_data1
INNER JOIN (
SELECT ClientID, MAX(TO_DAYS(DateEnd) - TO_DAYS(DateBegin)) as DateDiff
FROM table1 AS client_data
GROUP BY ClientID
) as client_data2
ON client_data1.ClientID = client_data2.ClientID
AND client_data2.DateDiff = (TO_DAYS(client_data1.DateEnd) - TO_DAYS(client_data1.DateBegin));
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com