distinct records help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

distinct records help

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-06-30 : 11:20:21

below is my current sp. my results return records that have the same email address. the distinct is working for all fields. so someone can have the same email address but if their last name is different, it shows two records with the same email address. how do i modify this so i only get one record for each email address?

SELECT distinct a.strEmailAddr, a.strFirstName, a.strLastName, a.strCity, a.strState,CONVERT(CHAR(10), dteArrivalTime, 101) AS dteArrivalTime,
blnCheckAcct,strCompanyName
FROM tbAddressInfo a
JOIN tbTrafficData t
ON a.varTransactionId = t.varTransactionId
JOIN tbMultiMarketLink mm
ON mm.varTransactionId = a.varTRansactionId
JOIN tbMultiLenderLookup ml
ON mm.zADID = ml.zADID
WHERE blnComplete = 0
AND a.blnMorePlease = 1
--email address validation
and a.strEmailAddr <> ''
and CHARINDEX(' ',LTRIM(RTRIM(a.strEmailAddr))) = 0 -- No embedded spaces
AND LEFT(LTRIM(a.strEmailAddr),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM(a.strEmailAddr),1) <> '.' -- '.' can't be the last character of an email address
AND CHARINDEX('.',a.strEmailAddr,CHARINDEX('@',a.strEmailAddr)) - CHARINDEX('@',a.strEmailAddr) > 1 -- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM(a.strEmailAddr))) - LEN(REPLACE(LTRIM(RTRIM(a.strEmailAddr)),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(a.strEmailAddr)))) >= 3 -- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',a.strEmailAddr) = 0 AND CHARINDEX('..',a.strEmailAddr) = 0) -- can't have patterns like '.@' and '..'

AND a.varTransactionId NOT IN (SELECT varTransactionId FROM tbBankData)
AND strEmailAddr NOT IN (SELECT strEmailAddr FROM tbapplicationstatus a JOIN tbAddressInfo d ON a.varTransactionId = d.varTransactionId WHERE varAppStatus IN (1,2,6,7))
order by strEmailAddr

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-30 : 11:44:08

Not sure if this will do it ....... but try it any way.

--***********************************************************

SELECT a.strEmailAddr, a.strFirstName, a.strLastName, a.strCity, a.strState,CONVERT(CHAR(10), dteArrivalTime, 101) AS dteArrivalTime,
blnCheckAcct,strCompanyName
FROM tbAddressInfo a
join (select stremailaddr, min(isnull(strFirstName,'') + isnull(strLastName,'') + isnull(strCity, '') + isnull(strState, '')) as joincrit
from tbAddressInfo
group by stremailaddr) as jct
on isnull(a.strFirstName,'') + isnull(a.strLastName,'') + isnull(a.strCity, '') + isnull(a.strState, '') = jct.joincrit
JOIN tbTrafficData t
ON a.varTransactionId = t.varTransactionId
JOIN tbMultiMarketLink mm
ON mm.varTransactionId = a.varTRansactionId
JOIN tbMultiLenderLookup ml
ON mm.zADID = ml.zADID
WHERE blnComplete = 0
AND a.blnMorePlease = 1
--email address validation
and a.strEmailAddr <> ''
and CHARINDEX(' ',LTRIM(RTRIM(a.strEmailAddr))) = 0 -- No embedded spaces
AND LEFT(LTRIM(a.strEmailAddr),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM(a.strEmailAddr),1) <> '.' -- '.' can't be the last character of an email address
AND CHARINDEX('.',a.strEmailAddr,CHARINDEX('@',a.strEmailAddr)) - CHARINDEX('@',a.strEmailAddr) > 1 -- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM(a.strEmailAddr))) - LEN(REPLACE(LTRIM(RTRIM(a.strEmailAddr)),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(a.strEmailAddr)))) >= 3 -- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',a.strEmailAddr) = 0 AND CHARINDEX('..',a.strEmailAddr) = 0) -- can't have patterns like '.@' and '..'

AND a.varTransactionId NOT IN (SELECT varTransactionId FROM tbBankData)
AND strEmailAddr NOT IN (SELECT strEmailAddr FROM tbapplicationstatus a JOIN tbAddressInfo d ON a.varTransactionId = d.varTransactionId WHERE varAppStatus IN (1,2,6,7))
order by strEmailAddr

Duane.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-06-30 : 11:47:56

i get ambiguous column errors with that

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-30 : 11:52:31

Try this then:

SELECT a.strEmailAddr, a.strFirstName, a.strLastName, a.strCity, a.strState,CONVERT(CHAR(10), dteArrivalTime, 101) AS dteArrivalTime,
blnCheckAcct,strCompanyName
FROM tbAddressInfo a
join (select stremailaddr, min(isnull(strFirstName,'') + isnull(strLastName,'') + isnull(strCity, '') + isnull(strState, '')) as joincrit
from tbAddressInfo
group by stremailaddr) as jct
on isnull(a.strFirstName,'') + isnull(a.strLastName,'') + isnull(a.strCity, '') + isnull(a.strState, '') = jct.joincrit
and jct.stremailaddr = a.strEmailAddr
JOIN tbTrafficData t
ON a.varTransactionId = t.varTransactionId
JOIN tbMultiMarketLink mm
ON mm.varTransactionId = a.varTRansactionId
JOIN tbMultiLenderLookup ml
ON mm.zADID = ml.zADID
WHERE blnComplete = 0
AND a.blnMorePlease = 1
--email address validation
and a.strEmailAddr <> ''
and CHARINDEX(' ',LTRIM(RTRIM(a.strEmailAddr))) = 0 -- No embedded spaces
AND LEFT(LTRIM(a.strEmailAddr),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM(a.strEmailAddr),1) <> '.' -- '.' can't be the last character of an email address
AND CHARINDEX('.',a.strEmailAddr,CHARINDEX('@',a.strEmailAddr)) - CHARINDEX('@',a.strEmailAddr) > 1 -- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM(a.strEmailAddr))) - LEN(REPLACE(LTRIM(RTRIM(a.strEmailAddr)),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(a.strEmailAddr)))) >= 3 -- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',a.strEmailAddr) = 0 AND CHARINDEX('..',a.strEmailAddr) = 0) -- can't have patterns like '.@' and '..'

AND a.varTransactionId NOT IN (SELECT varTransactionId FROM tbBankData)
AND a.strEmailAddr NOT IN (SELECT strEmailAddr FROM tbapplicationstatus a JOIN tbAddressInfo d ON a.varTransactionId = d.varTransactionId WHERE varAppStatus IN (1,2,6,7))
order by a.strEmailAddr

Duane.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-30 : 11:53:52

ambigouus column....means the same column name appears in 2 tables within the query and without a table qualifier (or table alias) the query manager is unable to determine which table the column is to be referenced from........

go thorugh the column names in both the SELECT statement, the WHERE and ON clauses ...and make sure every column referenced is 'fully qualified'

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-06-30 : 11:55:47

nope, didn't work either. no errors, but still getting duplicate email addresses

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-30 : 11:57:25

quote:
Originally posted by AndrewMurphy

ambigouus column....means the same column name appears in 2 tables within the query and without a table qualifier (or table alias) the query manager is unable to determine which table the column is to be referenced from........

go thorugh the column names in both the SELECT statement, the WHERE and ON clauses ...and make sure every column referenced is 'fully qualified'

I did that

I found the stremailaddr in the group by was not being prefixed with an a.

I made that change and I also realized that I needed to add more criteria to the join on the derived table.

I unfortunately can't test the code as I don't have sample data - but I think it will work

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-30 : 12:01:29

quote:
Originally posted by mikejohnson

nope, didn't work either. no errors, but still getting duplicate email addresses

I think that there are then duplicates at the following level:
strEmailAddr, strFirstName, strLastName, strCity, strState
in your addressinfo table.

Is that the case?

If so you might need to consider cleaning up duplicate info and perhaps fixing the cause

Duane.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-06-30 : 12:02:14

i have sample data

, and it doesn't work

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-30 : 12:03:59

Have you checked for duplicates at the level in my previous post?

Duane.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-06-30 : 12:05:34

maybe it's best if i put it like this:

if this is my table:

email name
----- ----
test@test.com test
test@test.com test2

i still only want to return one record even though the name is different in the records.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-06-30 : 12:06:26

yes duane, i've tried with all your posts

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-30 : 12:12:16

select email, min(name)
from mytable
group by email

That will do that.
but will not work in the example provided in your first post because there are many other fields coming back in the record set.

and min(field) will return the min value for the specified field asociated with the field in the group by.

This means that the other fields ie strFirstName, strLastName, strCity, strState
could be fields from 4 different records and not all from the same record

Duane.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-01 : 05:51:36

this sounds so familiar...

I'm sure someone solved something similar via something like

select top 1 * from tbAddressInfo
where (do all your distinct work) dis_tb

Can't remember the exact thecnique, but it was correlating the sub-query, and select top one per "set" of duplicates ... does that make ANY sense?

I'll search and see if I find it

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-01 : 06:31:37

Is something like


SELECT *
FROM   MyTable T1
       JOIN
       (
           SELECT MyPKColumn(s)
           FROM   MyTable
           WHERE (do all your distinct work)
       ) X
           ON X.MyPKColumn = T1.MyPKColumn

any use?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources