Comparison - 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

Comparison

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-09-30 : 11:51:05

Hi -
I have two tables, Q1 and Q2. I need to perform two tasks - 1) Identify those that were in Q1 and are still in Q2 and 2) Identify those that were in Q1 but are no longer in Q2.

I cannot remember how to do it. Can any one provide help??

Thanks!!

JB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 11:53:10

1, use INNER JOIN

2, use LEFT JOIN

syntax have a look at google and try
if you face any problem we will help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-09-30 : 12:07:55

Thanks!!

I got 1 to work:

select *
from dbo.Q1_import_sql_txt2 Q1 inner join dbo.Q2_import_sql_txt2 Q2 on q1.mbr_id = Q2.mbr_id

Now I cannot get 2 to work:

select *
from dbo.Q1_import_sql_txt2 Q1 left join dbo.Q2_import_sql_txt2 Q2 on q1.mbr_id = Q2.mbr_id

Can you help more with the second one?

Thanks very much!!

quote:
Originally posted by visakh16

1, use INNER JOIN

2, use LEFT JOIN

syntax have a look at google and try
if you face any problem we will help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:10:42

it should be


select *
from dbo.Q1_import_sql_txt2 Q1 left join dbo.Q2_import_sql_txt2 Q2 on q1.mbr_id = Q2.mbr_id
where Q2.mbr_id is null

the reason is because left join returns all the records from Q1 and ones which are present in Q2 will have matching fields and ones not in Q2 will have those fields as null. So using filter is null will make sure you return only ones not in Q2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-09-30 : 12:13:57

ahhhhhhh.....Thank you, I really appreciate the help!!

quote:
Originally posted by visakh16

it should be

select *
from dbo.Q1_import_sql_txt2 Q1 left join dbo.Q2_import_sql_txt2 Q2 on q1.mbr_id = Q2.mbr_id
where Q2.mbr_id is null

the reason is because left join returns all the records from Q1 and ones which are present in Q2 will have matching fields and ones not in Q2 will have those fields as null. So using filter is null will make sure you return only ones not in Q2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:15:27

no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-09-30 : 12:56:12

One more dumb question......

There is a second set of records with NULL in each row. Those would represent the records meeting the criteria set in the where clause, where Q2.mbr_id is null, correct??

quote:
Originally posted by visakh16

no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:10:36

quote:
Originally posted by jcb267

One more dumb question......

There is a second set of records with NULL in each row. Those would represent the records meeting the criteria set in the where clause, where Q2.mbr_id is null, correct??

quote:
Originally posted by visakh16

no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yep.proved NULL is coming for Q2 fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2011-09-30 : 13:33:17

Thanks again!

quote:
Originally posted by visakh16

quote:
Originally posted by jcb267

One more dumb question......

There is a second set of records with NULL in each row. Those would represent the records meeting the criteria set in the where clause, where Q2.mbr_id is null, correct??

quote:
Originally posted by visakh16

no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yep.proved NULL is coming for Q2 fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources