Sql-server – Return unique records based on shared column in 2 tables

join;sql-server-2008table

I'm very new to SQL and currently using SQL Server 2008 R2. I'm fairly certain that I need to use a join but not sure if that's possible. The goal is to return just the customer's email address based on their last visit to my store. Poking around, this is the query I started off with:

SELECT DISTINCT account
FROM   [main].[dbo].[visits]
WHERE  time IS NOT NULL
AND    date = '2017-05-25'

This correctly returned a list of customer account numbers for a particular day from table visits. Now I need to find the customer's email, but that is stored in a different table, custlist.

This is where I got lost. I tried some examples found on StackExchange but couldn't quite get the join logic down.

Any help is much appreciated, thank you!

Best Answer

Assuming Account is on CustList, the following example should get you going:

--Set up demo data
SET NOCOUNT ON

DECLARE @Visits TABLE (Id INT,Account INT,DateOfVisit DATE,TimeOfVisit TIME)
DECLARE @CustList TABLE (Id INT,Account INT,EmailAddress VARCHAR(100))

INSERT INTO @Visits (Id,Account,DateOfVisit,TimeOfVisit)
VALUES (1,1,'2017-05-25','08:00:00')
INSERT INTO @Visits (Id,Account,DateOfVisit,TimeOfVisit)
VALUES (1,1,'2017-05-25','10:00:00')

INSERT INTO @Custlist (Id,Account,EmailAddress)
VALUES (1,1,'MyEmail@Test.com');

--Common Table Expression for distince account numbers
WITH DistinctAccountsVisits
AS (
    SELECT DISTINCT Account
    FROM @visits
    WHERE TimeOfVisit IS NOT NULL
        AND DateOfVisit = '2017-05-25'
    )
--Join Visits with CustList on Account
SELECT v.Account
    ,c.EmailAddress
FROM DistinctAccountsvisits v
JOIN @CustList c ON c.Account = v.Account
Related Question