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:
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
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.
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:
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