query result help

[920506]

Hi,all,
I ran the two queries and I thought it would be the same, but it's different.
Can you explain to me.

Query 1: result---52 rows
select s.InsuredSurname, s.email from studyUSA s
join interMedical I on s.email=I.email
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and (s.agentcode not like '162%') and (s.agentcode not like '17%')
and s.agentcode <> '130844'


Query 2: result--14 rows

select s.InsuredSurname, s.email from tis_studyUSA s
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and s.email IN (Select I.Email from tis_InterMedical I)
and (agentcode not like '162%') and (agentcode not like '17%')
and agentcode <> '130844'

Thanks!
Betty

[MAK]

1st query - you are joining two tables on a column which is supposed to be a unique key but it is not. you have same email id repeating many times too.

2nd query is filtering the number of rows that matches a condition (not another table). So less rows.


Example:

use pubs
go
select * from authors where state ='CA'
go - 15 rows
select * from authors a join authors b
on a.state=b.state and a.state ='CA'
go --225 rows
select * from authors a join authors b
on a.state=b.state where a.state ='CA'
go --225 rows

[920506]

Mak,
Finally you came back. Thank you so much for your posting. Yes, you are right.
When I used distince key word in the following statement. The result are the same.

select distinct s.email,s.InsuredSurname from studyUSA s
join interMedical I on s.email=I.email
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and (s.agentcode not like '162%') and (s.agentcode not like '17%')


Now, I have one more question for you:

I have the following query:
select distinct s.email, s.InsuredSurname from studyUSA s
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and s.email NOT IN (Select I.Email from InterMedical I)
and s.email NOT IN (Select v.Email from VisitUSA V)
and s.email NOT IN (Select l.Email from select l)
and (agentcode not like '162%') and (agentcode not like '17%')
and agentcode <> '130844' order by s.email
and s.agentcode <> '130844'

This statement select the result to eliminate all the email addresses in three other tables(interMedical, visitUSa and select)
I have another similar query which is for WorldMed table, it needs to eliminate emails from those three tables too.
But how can I combine these two query statements then the result won't have redundent email addresses from table worldMed and StudyUSA?

[Rawhide]

You really need to avoid using the IN clause with a subquery. It's a real performance killer. Using an exists clause will give you much better performance. In fact, in this case, I would insert the email addresses from the other three tables into a table variable and do just 1 exists clause.

Declare @Emails Table (Email varchar(250))

Insert Into @Emails
Select Distinct Email
From InterMedical

Insert Into @Emails
Select Distinct Email
From VisitUSA

Insert Into @Emails
Select Distinct Email
From [select]

Select Distinct email, InsuredSurname
From studyUSA s
Where convert(char(10), enrolldate, 126)Between '2004-01-01' And '2005-05-20'
And agentcode not like '162%'
And agentcode not like '17%'
And agentcode <> '130844'
And Not Exists (Select 1 From @Emails Where Email = s.email)
Order By s.email




Then do the same query for your WorldMed table and do a UNION between the two queries. The UNION operator combines the recordsets into one and eliminates duplicates. In fact, if you're doing a UNION, you don't need to do a DISTINCT in the select statements because then it's just redundant.

[920506]

Hi,rawhide,
Thank you so much for your help!
Your solution is a better option. I will keep it in mind and try that later on.

Yesterday, I had to write some asp program to remove those duplicate email records after I use Union to join the data from both two tables.
Because of the very interesting thing :The distinct key word used in select statement(with many columns to be selected) will only give you the distinct rows which could have many duplicated email (but different other column data) records.
So with your table email only has one column, the distinct will work out. I never knew that Union will automaticlly eliminate the duplicate rows.

Betty

[Rawhide]

If for some reason you don't want duplicates filtered out of a Union, use "Union All". It's the same functiona s Union just without filtering of duplicates.