Eliminate duplicate entries

nlicata · September 16th, 2003, 12:35 PM

I request data and it returns fine, but I am creating a mailing list for my boss,
and need to eliminate email duplicates. I am proficient in ASP which is the interface
he will use - but I thought eliminating duplicates at query level was better. I show example data below
but keep in mind the numbers are returning fine and the emails return fine but the example shows the
duplicate entry for email address can appear in any column or row...matching with any other column and row...
you could have the same email for offices but with different associated rep email addresses... works like a tree
where regions are over offices and offices over reps.

thanks for any help...

This is a rough look at my tables - which I can't alter.

sales_representative sales_office sales_region
__________________________________________________ ______________________
sales_region_number sales_office_number sales_region_number
sales_office_number email_address email_address
sales_rep_number
email_address

Here is my select statement:
__________________________________________________ __________________________
Select sales_representative.sales_rep_number,sales_repres entative.email_address,
    sales_office.sales_office_number,sales_office.emai l_address,
    sales_region.sales_region_number,sales_region.emai l_address

FROM sales_representative LEFT JOIN sales_office
    ON (sales_representative.sales_office_number=sales_of fice.sales_office_number)
        JOIN sales_region
            ON (sales_representative.sales_region_number=sales_re gion.sales_region_number)

sample data return in query analyzer
__________________________________________________ ________________________________________

sales_rep_number - email_address - sales_office_number - email_address - sales_region_number - email_address
0000001 [email protected] 0000001 [email protected] 0000001 [email protected]
0000007 [email protected] 0000003 [email protected] 00000007 [email protected]
00000010 [email protected] 00005 [email protected] 000004 [email protected]

Jeff Mason · September 17th, 2003, 10:20 AM

So what do you want the mailing list to look like? The query you posted is a perfectly reasonable one (although I wonder about the outer join - you have sales reps not in a sales office? Where are they located?) with one row per sales rep showing interesting related information about that rep, which of, course, isn't a mailing list.

Are you just looking for a list of unique email addresses? If so, a UNION query selecting the email address from each table and combining the SELECT statements with the UNION operator by default eliminates duplicates, e.g.:

Code:

SELECT email_address FROM sales_representative
UNION
SELECT email_address FROM sales_office
UNION
SELECT email_address FROM sales_region;

will give a list of the email addresses eliminating duplicates...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

nlicata · September 17th, 2003, 10:37 AM

Thanks Jeff,

I ended up doing the code below in the meantime waiting for help. Each table has an email field for the rep, office, or region respectively. I can do a -- Select email_address from sales_region -- and get a perfectly good list for mailing... But when I do the same select statement from all tables there are dups across columns and rows because some reps are also the office and the region because it is a one man business. I dont want that person who is the region, office, and rep to receive 3 emails. Also some persons (like my boss of the main company these reps work for) are listed in the email field not only on the same row (corresponding records made with join) but also in different columns. He is the region email for many different offices and reps. The code below produces a list exactly like I need but in 3 seperate result windows. In the asp bulk mail web page interface I made it just iterates through the 3 queries appending the results. It would be nice to have a single query so i do not have to make 3 recordset objects (or at least 3 different sql statements) in asp. I wanted to respond but I am going to try the UNION now - thanks again!

BTW UPDATE- just tried it quickly and the union was perfect - I never saw the union anywhere I learn from (www3schools.com, etc.) thanks again - I learned something....

Nick

SELECT DISTINCT sales_representative.email_address
FROM sales_representative
WHERE (sales_representative.email_address <> 'NULL')
order by sales_representative.email_address ;

SELECT DISTINCT sales_office.email_address
FROM sales_office
WHERE (sales_office.email_address <> 'NULL') AND
    (sales_office.email_address NOT IN (SELECT DISTINCT sales_representative.email_address
                        FROM sales_representative
                        WHERE (sales_representative.email_address <> 'NULL') ))

order by sales_office.email_address ;

SELECT DISTINCT sales_region.email_address
FROM sales_region
WHERE (sales_region.email_address <> 'NULL') AND
    (sales_region.email_address NOT IN (SELECT DISTINCT sales_office.email_address
                        FROM sales_office
                        WHERE (sales_office.email_address <> 'NULL') )) AND
    (sales_region.email_address NOT IN (SELECT DISTINCT sales_representative.email_address
                        FROM sales_representative
                        WHERE (sales_representative.email_address <> 'NULL') ))
order by sales_region.email_address ;