fining the common records in two tables

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

fining the common records in two tables

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-08-26 : 14:23:46

I have two tables I want to create a view to include the common rows in two tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-26 : 14:38:07

Define "common rows" for us please. Does every column have to be identical? Or just the primary key?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

goligol
Posting Yak Master

128 Posts

Posted - 2011-08-26 : 14:58:55

Thank you.

the format of both tables is:
O D M C T0

I want the record with the same key set of (ODMC)but T0 of both wich could be different: The following is my final desire of common rows of having same ODMC in both tables.

O D M C T0 T0

gwilson67
Starting Member

42 Posts

Posted - 2011-08-26 : 14:59:12

Trying using the following query:

create view myView1
as
select field1, field2, field3 from myTable
Intersept
select field1, field2, field3 from myTable2

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-26 : 15:16:36

SELECT t1.O,t1.D,t1.M,t1.C,t1.T0,t2.TO
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.O = t2.O and t1.D = t2.D and t1.M = t2.M and t1.C= t2.C

Jim

Everyday I learn something that somebody else already knew

goligol
Posting Yak Master

128 Posts

Posted - 2011-08-26 : 15:39:46

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 04:39:56

quote:
Originally posted by gwilson67

Trying using the following query:

create view myView1
as
select field1, field2, field3 from myTable
~~Intersept~~intersect
select field1, field2, field3 from myTable2

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development

small typo

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 04:42:24

will you have matching records with OMDC field values in both the tableS?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources