0

This is the database schema that I'm working with and I've included some sample data as an example of what is currently in the database.

CREATE TABLE Visits   ([ADATE] date, [AKEYTIME] varchar(10), [ATIME] varchar(10), [BOOK] varchar(10), [ID] varchar(100), [ACCOUNT] int,
                       [ADESC] varchar(100), [DEPARTMENT] varchar(100), [FINTIME] varchar (10), [FACILITY] varchar(10), [APPTNO] int);
CREATE TABLE CustList ([ACCOUNT] int, [FAMILY] int, [CLNAME] varchar(100), [CFNAME] varchar(100), [CMNAME] varchar(100), [CPHONE] varchar(12),
                       [CID] int, [SEX] varchar(1), [NICKNAME] varchar(100), [EMAIL] varchar(100));
CREATE TABLE Orders   ([ID] int, [DATETIME] datetime, [SSNO] int, [NAME] varchar(100), [ORDERDATE] date, [ORDERTIME] varchar(10),
                       [DONEDATE] date, [DONETIME] varchar(10), [PAGE] int, [ACCOUNT] int, [FACILITY] varchar(100));

INSERT INTO Visits
    ([ADATE], [AKEYTIME], [ATIME], [BOOK], [ID], [ACCOUNT], [ADESC], [DEPARTMENT], [FINTIME], [FACILITY], [APPTNO])
VALUES
    ('', '11:18', '11:18 AM', 'HD1_WC', 'SMITH, JOHN', '131456', 'REPAIR ORDER', 'HD', '11:35 AM', '', '2002467341'),
    ('', '15:21', '03:21 PM', 'XA1_NU', 'JONES, JANE', '343789', 'NEW PURCHASE', 'XA', '3:35 PM', '', '2002763299'),
    ('', '15:30', '03:30 PM', 'XA1_NU', 'JONES, JANE', '343789', 'NEW PURCHASE', 'XA', '3:35 PM', '', '2002763244')

INSERT INTO CustList
    ([ACCOUNT], [FAMILY], [CLNAME], [CFNAME], [CMNAME], [CPHONE], [CID], [SEX], [NICKNAME], [EMAIL])
VALUES
    ('131456', '131456', 'SMITH', 'JOHN', '', '111/222-3333', '131456', 'M', 'JOHN', 'JOHN@EMAIL.COM'),
    ('343789', '343789', 'JONES', 'JANE', '', '111/444-5555', '343789', 'F', 'JANIE', 'JANE@EMAIL.COM')

INSERT INTO Orders
    ([ID], [DATETIME], [SSNO], [NAME], [ORDERDATE], [ORDERTIME], [DONEDATE], [DONETIME], [PAGE], [ACCOUNT], [FACILITY])
VALUES
    ('0001457843', '', '343789', 'JONES, JANE', '', '03:35 PM', '', '', '2002763299', '343789', 'OFFICE 1'),
    ('0001457844', '', '343789', 'JONES, JANE', '', '03:35 PM', '', '', '2002763299', '343789', 'OFFICE 1'),
    ('0001457845', '', '343789', 'JONES, JANE', '', '03:35 PM', '', '', '2002763244', '343789', 'OFFICE 1'),
    ('0001457846', '', '343789', 'JONES, JANE', '', '03:35 PM', '', '', '2002763244', '343789', 'OFFICE 1'),
    ('0001237789', '', '131456', 'SMITH, JOHN', '', '11:35 AM', '', '', '2002467341', '131456', 'OFFICE 2'),
    ('0001237790', '', '131456', 'SMITH, JOHN', '', '11:35 AM', '', '', '2002467341', '131456', 'OFFICE 2')
;

I've been using the following query to return the email address of a customer.

WITH DistinctAccountsVisits
AS (
    SELECT DISTINCT ACCOUNT
    FROM Visits
    WHERE FINTIME IS NOT NULL
         AND ADATE = '1900-01-01'
    )

SELECT v.Account
    ,c.Email
FROM DistinctAccountsvisits v
JOIN CustList c ON c.Account = v.Account

Now I need to pull information on which facility the customer last visited and that information is stored in a third table called Orders.

Here is some sample output that shows the duplicate entries that are returned when running the query in the below answer.

ID       EMAIL            FACILITY

123456  cust1@email.com   LOCATION1
123456  cust1@email.com   LOCATION2
123456  cust1@email.com   LOCATION2
123456  cust1@email.com   LOCATION1
123456  cust1@email.com   LOCATION1
123456  cust1@email.com   LOCATION3
123456  cust1@email.com   LOCATION3
123456  cust1@email.com   LOCATION3
123456  cust1@email.com   LOCATION1
987654  cust2@email.com   LOCATION1
987654  cust2@email.com   LOCATION1
987654  cust2@email.com   LOCATION1
987654  cust2@email.com   LOCATION1
987654  cust2@email.com   LOCATION3
987654  cust2@email.com   LOCATION1
987654  cust2@email.com   LOCATION2
987654  cust2@email.com   LOCATION2
987654  cust2@email.com   LOCATION2

DB Fiddle Examples using SQL Server 2016:

The query I'm using above was originally given to me by someone on StackExchange in a question I asked 4 years ago. The person who answered that question also asked the same thing but posted an answer before I could respond. Maybe that will give you more insight as I don't know how to get the CREATE TABLE DDL, I don't even know what DDL means.

Here is a link to the original question: Return unique records based on shared column in 2 tables

0
0

Based on the information you've provided, it seems like all you would need to do is another JOIN (which is an INNER JOIN) to the @MasterList table variable like this:

WITH DistinctAccountsVisits
AS (
    SELECT DISTINCT Account
    FROM @visits
    WHERE TimeOfVisit IS NOT NULL
         AND DateOfVisit = '2017-05-25'
    )

SELECT v.Account
    ,c.EmailAddress
    ,m.Facility
FROM DistinctAccountsvisits v
JOIN @CustList c ON c.Account = v.Account
JOIN @MasterList m ON v.Account = m.Account

If there's any issues with this, please let me know, and I can adjust accordingly. For example, this will filter out any rows from @DistinctAccountsvisits that don't relate to @MasterList. If you want to keep those rows in, then you can use a LEFT JOIN instead of JOIN.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.