Removing duplicates from INNER JOIN of two tables

[rgothard]

OK.. I'm trying to write a query which combines data from two tables. (I'm pretty novice at SQL Queries, so bear with me!) However, I want to exclude duplicate data (based on just one column -- an address field). Basically, I want to only have one result per address. The entire row won't be unique, however, because one or more fields will be different.

Here's the query I have:

[code]
SELECT tblEmailClub.emailclub_id,
tblEmailClub.firstname,
tblEmailClub.lastname,
tblEmailClub.email,
tblSourceHistory.datereg,
tblEmailClub.active,
tblEmailClub.address1,
tblEmailClub.address2,
tblEmailClub.city,
tblEmailClub.state,
tblEmailClub.zip,
tblSourceHistory.source FROM tblEmailClub
INNER JOIN tblSourceHistory
ON tblEmailClub.emailclub_id = tblSourceHistory.emailclub_id
WHERE tblSourceHistory.dateReg BETWEEN '3/1/2009' and '4/1/2009'
AND tblSourceHistory.source LIKE '%family.asp%'
ORDER BY tblSourceHistory.datereg asc
[/code]

The field I want to be unique is "tblEmailClub.address1". For instance, Person A enters data in my form twice (because they think they'll get two coupons), but "address1" is the same, but one or more other fields are unique, so a DISTINCT won't work because the entire row is not a duplicate.

Any thoughts?

On a related note, I had this query, which works great for pulling unique records (based on address1) from just my tblEmailClub, but I can't figure out how to combine it with my INNER JOIN above:

[code]
SELECT *
FROM tblemailclub t
JOIN (SELECT address1, emailclub_id = MIN(emailclub_id)
FROM tblemailclub
GROUP BY address1) AS t1 ON t.emailclub_id = t1.emailclub_id
where source like '%family.asp%'
and datereg between '1/1/2008' and '1/24/2008'
order by datereg asc
[/code]

Thanks!

[Ramy Mahrous]

Use Distinct keyword

[rgothard]

Use Distinct keyword

I actually tried to used DISTINCT, but it didn't work because other columns in the table (namely tblSourceHistory.source) is unique per record, so the DISTINCT thinks the entire record is unique instead of filtering out records that have a duplicate 'address1' field.

[Ramy Mahrous]

You say it doesn't work in this way

SELECT tblEmailClub.emailclub_id,
tblEmailClub.firstname,
tblEmailClub.lastname,
tblEmailClub.email,
tblSourceHistory.datereg,
tblEmailClub.active,
Distinct (tblEmailClub.address1),
tblEmailClub.address2,
tblEmailClub.city,
tblEmailClub.state,
tblEmailClub.zip,
tblSourceHistory.source FROM tblEmailClub
INNER JOIN tblSourceHistory
ON tblEmailClub.emailclub_id = tblSourceHistory.emailclub_id
WHERE tblSourceHistory.dateReg BETWEEN '3/1/2009' and '4/1/2009'
AND tblSourceHistory.source LIKE '%family.asp%'
ORDER BY tblSourceHistory.datereg asc

[rgothard]

Yeah, when I try to put the DISTINCT function there, I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'Distinct'.

Any other thoughts?
Thanks!!

[Ramy Mahrous]

hmmm, That's because of obligation of being distinct in first place

SELECT 
DISTINCT (tblEmailClub.address1),
tblEmailClub.emailclub_id,
tblEmailClub.firstname,
tblEmailClub.lastname,
tblEmailClub.email,
tblSourceHistory.datereg,
tblEmailClub.active,
tblEmailClub.address2,
tblEmailClub.city,
tblEmailClub.state,
tblEmailClub.zip,
tblSourceHistory.source FROM tblEmailClub
INNER JOIN tblSourceHistory
ON tblEmailClub.emailclub_id = tblSourceHistory.emailclub_id
WHERE tblSourceHistory.dateReg BETWEEN '3/1/2009' AND '4/1/2009'
AND tblSourceHistory.source LIKE '%family.asp%'
ORDER BY tblSourceHistory.datereg ASC

[rgothard]

Still no luck. That query still pulls in the duplicate 'address1' fields. (Assuming because it is thinking the entire record is distinct due to other fields being different.)

[Ramy Mahrous]

No, distinct works on column and it removes the duplication, I tried it myself.

[Ramy Mahrous]

You've right, I'll try something else and reply you.

[Ramy Mahrous]

After digging I came with bad performance solution :s but I think it works,

SELECT 
T1.address1,
tblEmailClub.emailclub_id,
tblEmailClub.firstname,
tblEmailClub.lastname,
tblEmailClub.email,
tblSourceHistory.datereg,
tblEmailClub.active,
tblEmailClub.address2,
tblEmailClub.city,
tblEmailClub.state,
tblEmailClub.zip,
tblSourceHistory.source FROM tblEmailClub
INNER JOIN tblSourceHistory
ON tblEmailClub.emailclub_id = tblSourceHistory.emailclub_id
WHERE tblSourceHistory.dateReg BETWEEN '3/1/2009' AND '4/1/2009' 
AND T1. address1 IN
(SELECT T2.address1 
FROM tblEmailClub AS T2
GROUP BY T2. address1 
HAVING COUNT(T2.address1)=1
)
AND tblSourceHistory.source LIKE '%family.asp%'
ORDER BY tblSourceHistory.datereg ASC

Please check it if you have any problem please contact me.
NOTE: try to read also in distinctrow it may help you http://office.microsoft.com/en-us/access/HP010322051033.aspx

I got this solution from my friend Ahmed Gamal: http://vb4arab.com/vb/member.php?u=3

[rgothard]

With that query, I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

The column prefix 'T1' does not match with a table name or alias name used in the query.

Any other ideas?
(Thanks so much for helping me out on this!)

[Ramy Mahrous]

Remove alias and use the table name directly. Replace every T1 & T2 with tblEmailClub

[rgothard]

Awesome. That did the trick!

Thanks so much!