Need Help Writing a Query - 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

General SQL Server Forums

New to SQL Server Programming

Need Help Writing a Query

Author

Topic

kpritchett
Starting Member

4 Posts

Posted - 2006-06-07 : 21:44:03

Help! I need to write a query that looks at one table with two columns. One column has 20K records and the second column has 25K records. I need to compare the two columns and pull out the difference between the two columns. The majority of the two columns data is the same but there are some that do not have a match between the two. The data is not in any current order. HELP! THANKS!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 21:56:36

"one table with two columns. One column has 20K records and the second column has 25K records"
Can you explain more on this ? If the 2 columns are of the same table, it should have same number of records. If the table has 20K records, then all the columns will have 20K records.

Or do you mean something like the table of 50K records, One Column has 20K records with value (30K are NULL) and the other column has 25K records with value (not null) and 25K records are NULL ?

Or It is in 2 separate table ?

Can you post your table structure, some sample data and the result that you want ?

kpritchett
Starting Member

4 Posts

Posted - 2006-06-07 : 22:04:18

Sure. I have a table name TravelEmail(EmailAddress1(text 75), EmailAddress2(text75). There are 20K of data in EmailAddress1 and 25K in EmailAddress2. I need to compare the two and pull back the ones that brings back no matches. EmailAddress1 <> EmailAddress2
Does this help. I appreciate your help.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-07 : 22:06:00

what is your criteria for difference? comparing 20K against 25K sounds vague, the number of rows itself is already a difference, how do you determine content difference? just the same row like...
col1<>col2?

--------------------
keeping it simple...

kpritchett
Starting Member

4 Posts

Posted - 2006-06-07 : 22:12:58

I have been given these email names to send surveys to and before I send them I need to eliminate the duplicate email addresses. They have aready been mailed. I just need to mail the unique addresses. Unique is an email that is not in both EmailAddress1 & EmailAddress2.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-07 : 22:31:57

then you just need to

select emailaddress1,emailaddress2 from travelemail
where emailaddress1<>emailaddress2

or am i oversimplifying things again?

--------------------
keeping it simple...

kpritchett
Starting Member

4 Posts

Posted - 2006-06-07 : 22:44:15

No this returns all the rows in both columns that do not match up. Should I seperate into two seperate tables?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-07 : 22:58:25

if your requirement is to send out emails that are unique in your table...

select email1 from (
select email1 from travelemail
union all
select email2 from travelemail)
t
group by email1
having count(*)=1

but if your requirement is to send out just one email per unique email address then... instead of union all, use union only

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 23:11:19

use table alias and inner join on not equal

select e1.emailaddress1, e2.emailaddress2 
from  travelemail e1 inner join travelemail e2
on    e1.emailaddress1 <> e2.emailaddress2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources