help please

[ChrisBeach]

Hi All

I have multiple tables... all with the same composite key. well the tables are all the same layout infact.. just each table has a different row of data in each for ex.

table 1
ID field1 field2
1 test 0
2 box 0
3 three 0
4 can 0
5 meh 0

table 2
ID field1 field2
1 0 paper
2 0 envelope
3 0 box
4 0 lighter
5 0 jack

Now I combined both of these tables into one.. but its just like having the table ontop of the other.. i need to combine them both based on the key, whats the best way to do this? In the insert statement making sure the keys are equal when adding to the table, or elsewhere?

TIA

 

[JayKusch]

What is the composite key made up of ????

ID + Field1 + Field2 OR
ID + Field1 OR
ID + Feild2 OR
Field1 + Field2 ?????


Thanks

J. Kusch

 

[ChrisBeach]

the composite key is 4 fields.. then the rest of the data is about 14 fields after that.. i just tried simplifying it with my example by making ID the primary key

 

[JayKusch]

So if I read your post correctly ... the final result w/ be 1 composite key of 4 fields + 28 other data columns (14 columns from Table1 and 14 columns from Table2)

IF SO ... you need to create your new table "TableC" somewhat like ...

TableC
-------------------------------------
ID (Being a compsite key of 4 fields)
Field1T1
Field2T1
Field3T1 ... rest of the 25 fields from Table1
Field28T1
Field1T2
Field2T2
Field3T2 ... rest of the 25 fields from Table2
Field28T2

Then you would do an

INSERT INTO TableC
  (ID,
   Field1T1
   Field2T1
   Field3T1 ... rest of the 25 fields from Table1
   Field28T1
   Field1T2
   Field2T2
   Field3T2 ... rest of the 25 fields from Table2)
   Field28T2

SELECT 
   t1.ID
   t1.Field1
   t1.Field2
   t1.Field3 ... rest of the 25 fields from Table1
   t1.Field28
   t2.Field1
   t2.Field2
   t2.Field3 ... rest of the 25 fields from Table2)
   t2.Field28
FROM Table1 as t1
JOIN Table2 as t2
ON (t1.ID = t2.ID)






Thanks

J. Kusch

 

[ChrisBeach]

Thanks alot! very helpful! Very much appreciated

 

[JayKusch]

Code was a "bit off" but should look more like ...

INSERT INTO TableC
  (ID,
   Field1T1
   Field2T1
   Field3T1 ...
   Field28T1
   Field1T2
   Field2T2
   Field3T2 ...
   Field28T2)

SELECT 
   t1.ID
   t1.Field1
   t1.Field2
   t1.Field3 ... rest of the 25 fields from Table1
   t1.Field28
   t2.Field1
   t2.Field2
   t2.Field3 ... rest of the 25 fields from Table2)
   t2.Field28
FROM Table1 as t1
JOIN Table2 as t2
ON (t1.ID = t2.ID)

Thanks

J. Kusch