Create a table with the common elements of 2 others

Create a table with the common elements of 2 others

Post by John Revil » Sun, 05 Sep 1999 04:00:00



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

Post by blev » Mon, 06 Sep 1999 04:00:00


how about

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

Post by Roy Harve » Wed, 08 Sep 1999 04:00:00


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

Post by Charles Bretana Jr » Wed, 08 Sep 1999 04:00:00


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