## Create a table with the common elements of 2 others

### Create a table with the common elements of 2 others

Hi All,
The reason I need to do this is long winded, so I wont emplain in to much
detail.

What I need is to create a table where comparing to contents of 2 other
identically structed tables and returning all the records that exist in
both.

This is not difficult for my small tables with 5 columns

SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5
FROM T1, T2
WHERE T1.col1 = T2.col1 AND
T1.col2 = T2.col2 AND
T1.col3 = T2.col3 AND
T1.col4 = T2.col4 AND
T1.col5 = T2.col5

However, Two of my tables have in excess of 60 columns. I started on the
query, but thought there must be a better way.

Can anyone help?

Regards,
John Revill

### Create a table with the common elements of 2 others

select * from t1, t2 where ....

Quote:> Hi All,
>   The reason I need to do this is long winded, so I wont emplain in to
much
> detail.

> What I need is to create a table where comparing to contents of 2 other
> identically structed tables and returning all the records that exist in
> both.

> This is not difficult for my small tables with 5 columns

> SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5
> FROM T1, T2
> WHERE T1.col1 = T2.col1 AND
>       T1.col2 = T2.col2 AND
>       T1.col3 = T2.col3 AND
>       T1.col4 = T2.col4 AND
>       T1.col5 = T2.col5

> However, Two of my tables have in excess of 60 columns. I started on the
> query, but thought there must be a better way.

> Can anyone help?

> Regards,
> John Revill

### Create a table with the common elements of 2 others

John,

Quote:>What I need is to create a table where comparing to contents of 2 other
>identically structed tables and returning all the records that exist in
>both.

>This is not difficult for my small tables with 5 columns

>SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5
>FROM T1, T2
>WHERE T1.col1 = T2.col1 AND
>      T1.col2 = T2.col2 AND
>      T1.col3 = T2.col3 AND
>      T1.col4 = T2.col4 AND
>      T1.col5 = T2.col5

>However, Two of my tables have in excess of 60 columns. I started on the
>query, but thought there must be a better way.

Your example is exactly what you need.  I sometimes save on the typing
by writting a query against syscolumns that formats things for me.  An
example of this sort of shortcut to give you some ideas is below.

Roy

select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE '       '
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + '  FROM ' + O.name
END
from sysobjects O, syscolumns C
where O.id = C.id

order by C.colid

### Create a table with the common elements of 2 others

If you had a Primary Key on the two tables which were relationally joined to
one another, (One was a Foreign key to the PK of the other, this would be
easy....

But assuming you don't from the question you are asking, you either have to
do your 60 part join, or do a UNION ALL and then group by with a count, and
restrict the output to thsoe records having Count = 2.....

Select * From
(Select * From Table1
UNION ALL
Select * From Table2)
Group By <Here list all columns in table>
Having Count(*) = 2

Charly

Quote:> Hi All,
>   The reason I need to do this is long winded, so I wont emplain in to
much
> detail.

> What I need is to create a table where comparing to contents of 2 other
> identically structed tables and returning all the records that exist in
> both.

> This is not difficult for my small tables with 5 columns

> SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5
> FROM T1, T2
> WHERE T1.col1 = T2.col1 AND
>       T1.col2 = T2.col2 AND
>       T1.col3 = T2.col3 AND
>       T1.col4 = T2.col4 AND
>       T1.col5 = T2.col5

> However, Two of my tables have in excess of 60 columns. I started on the
> query, but thought there must be a better way.

> Can anyone help?

> Regards,
> John Revill