SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE ClientTable (
  ContactID INT,
  Name VARCHAR(25),
  Email VARCHAR(250)
);

CREATE TABLE TravelTable (
  ContactID INT,
  TripDate DATE,
  Country VARCHAR(25)
);

INSERT INTO ClientTable
VALUES
(001, 'John', 'johnemail01@gmail.com'),
(002, 'Maria', 'mariaemail02@gmail.com'),
(003, 'Carlos', 'carlosemail03@gmail.com'),
(004, 'Patricia', 'patriciaemail04@gmail.com');

INSERT INTO TravelTable
VALUES
(001, '2022-10-18', 'Germany'),
(003, '2022-02-05', 'Canada'),
(001, '2022-03-07', 'EUA'),
(002, '2022-07-02', 'India'),
(004, '2022-01-28', 'Austria'),
(003, '2022-01-28', 'Mexico');
10 rows affected

SELECT * FROM ClientTable;
SELECT * FROM TravelTable;


ContactID
Name
Email


1
John
johnemail01@gmail.com

2
Maria
mariaemail02@gmail.com

3
Carlos
carlosemail03@gmail.com

4
Patricia
patriciaemail04@gmail.com




ContactID
TripDate
Country


1
2022-10-18
Germany

3
2022-02-05
Canada

1
2022-03-07
EUA

2
2022-07-02
India

4
2022-01-28
Austria

3
2022-01-28
Mexico


SELECT 
  a.ContactID, 
  a.Name, 
  a.Email, 
  b.TripDate AS 'LastTrip', 
  b.Country
FROM ClientTable a WITH (NOLOCK)
LEFT JOIN TravelTable b ON a.ContactID = b.ContactID
WHERE a.ContactID IS NOT NULL 
  AND a.Email IS NOT NULL
  AND b.TripDate = (SELECT MAX(c.TripDate) 
                    FROM TravelTable c)


ContactID
Name
Email
LastTrip
Country


1
John
johnemail01@gmail.com
2022-10-18
Germany

ContactID	Name	Email
1	John	johnemail01@gmail.com
2	Maria	mariaemail02@gmail.com
3	Carlos	carlosemail03@gmail.com
4	Patricia	patriciaemail04@gmail.com

ContactID	TripDate	Country
1	2022-10-18	Germany
3	2022-02-05	Canada
1	2022-03-07	EUA
2	2022-07-02	India
4	2022-01-28	Austria
3	2022-01-28	Mexico