How to filter data?

coolest_pie · May 7th, 2004, 09:34 AM

Hi,
I have two tables of same structure on SQL server 2000
table structure:
1.name
2.address
3.telephone

Now, table A containes over 10000 records adn table B contains around 500 records. Also, table B contain some data which are already present in table A and some are not present in table A.

Now I want to filter these data into two different tables which are present in table A and which are not in table A.

How this can be done on SQL server 2000. Can anyone suggest me some procedure code to help me out.
Im in dire need of it...please help me out.

Looking for an earliets post!!!

:(jay

Hal Levy · May 7th, 2004, 10:42 AM

You can use JOIN and IN statements to make these checks.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

coolest_pie · May 7th, 2004, 11:37 AM

but how...im an newbie...

Can you please help me with extact code that can run on SQL SERVER 2000 using structure of the table mentioned.

I would be obliged for any kind of help.

Thanks in advqnce!!!!

Hal Levy · May 7th, 2004, 11:46 AM

No, I can't write your code for you. I gave you a pointer to the solution by telling you what commands to use. Now search on google and learn about these commands.

Or, if you wish, you can pay me to write the code for you. It is what I get paid to do- why should I give it away free?

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

coolest_pie · May 7th, 2004, 11:52 AM

im not asking you to write code for me...i am asking all people on net to help me out...if they can???

happygv · May 7th, 2004, 01:36 PM

If you want to trade, please get off. This is the place where we share our knowledge and make others too grow.

Jay, here you go.

There are different ways to get there.

For count of similar records

Code:

Select count(b.*)
from TABLEB b, TABLEA a
where b.Name=a.Name

For listing similar records

Code:

Select b.*
from TABLEB b, TABLEA a
where b.Name=a.Name

For listing unmatched records, (Considering that TABLEB has the unmatched records)

Code:

Select b.*
from TABLEB b, TABLEA a
where b.Name<>a.Name

OR

Code:

Select b.*
from TABLEB b
where b.Name NOT in (Select a.Name from TABLEA a)

You can also use IN operator to achieve this.
If you are interested, I would post that too describing how to do that.

There is always someone to help.

Cheers!

-Vijay G

Hal Levy · May 7th, 2004, 02:20 PM

No Vijay, I want him to do his own job and not ask me to do it for him. I told him how it can be done. Giving him the code doesn't help him learn how to do a similar thing in the future.

Since WROX is about learning (that's why the books are there, to teach). It seems like the best way to help him is to direct him to the answer - not give it to him. Help him LEARN how to do it for himself next time and you have really given him assistance.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

happygv · May 7th, 2004, 07:00 PM

Hal,

Seeing the nature of the question, I thought he is kind of a beginner, So in order to make him get more interest in it I showed different ways to do that and go on from there. Kind of creating interest in him is what I wanted to do.

Didn't really wanted to make him lazy.

You are also right in a way.:)
Cheers!

-Vijay G