Looking at 3 Tables - Recipient, HouseholdScores, Sales.
I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date information in the Recipient Table so i can only join the two tables on iCustomerID.
What I have Tried (Using - 'Microsoft SQL Server Management studio 17':
SELECT DISTINCT iRecipientId as RecipientID,NmsRecipient.iCustomerId as Cust_Num,sFirstName as FirstName,sLastName as LastName,sEmail as Email, iBlacklistPostalMail as NoMail,iBlacklistEmail as NoEmail,iBlacklistMailGCCL as DontMailGCCL,iBlacklistGCCL as DontEmailGCCL, iBlacklistMailOAT as DontMailOAT,iBlacklistOAT as DontEmailOAT,sLastBrand as LBT,sFrequencyDetail as Frequency,sRecency as Recency,GccTbl_HouseholdScores.iNumTripsBkd as Trips, GccTbl_Sales.tsDepart as Departure_Date FROM ((neolane.NmsRecipient Inner Join [neolane].[GccTbl_HouseholdScores] ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId) Inner Join [neolane].[GccTbl_Sales] ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId) WHERE iIrateFlag= 1 AND iOperationalFlag= 1 AND iEmployeeFlag= 0 AND iDeceased= 0 Order By iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;
Results are bringing back Duplicate iCustomerID where i only want 1 instance for each with the Most Recent Departure date