How do i append tables

[mita]

I have got two tables
table 1 has data for year 2000 as follows
Company ID Units_jan Units_feb............Units_Dec
ABX 12 3 5 .............45
34 5 454 354354
................

table 2 has data for year 2001 as follows
Company ID Units_jan Units_feb............Units_Dec
AwX 12 3 5 .............45
34 5 454 354354
................
what i want is something like this

Company ID Units_jan Units_feb............Units_Dec Units_jan units feb
ABC 23

ABX 12 3 5 .............45

AwX 12



how do i do this???

[CaptainD]

First to answer your question if I read it right. With the example you give, just do a join on the company name since what you show does not appear to have a unique table ID number and no unique ID number to represent the company.

SELECT tTable1.sCompanyName, tTable1.Units_Jan01, tTable1.Units_Feb01, tTable2.Units_Jan02, tTable2.Units_Feb02
FROM tTable1 INNER JOIN tTable2 ON tTable1.sCompanyName = tTable2.sCompanyName;
 

Now, posting code(or examples). you can use "[" code "]" Write Code Here "[" /code "]" (remove the quotes around the "[" "]" brackets to desplay the code in the boxes you see here. makes it easier to read and you can cut and paste out of them.

As far as your table designs go, you need to look into what is called "normal Form" to design your tables correctly, it will make working with the databases a lot easier.

[Owais]

Captain u r quite right if the tables are normalized here. However i think that it would be jsut a simple query without join cuz in this way he will be able to get the data form both tables. If join condition is there then he will be getting only the common tuples from both tables. Just a clarification to your answer. I might be wrong here.

[CaptainD]

Owais is correct, with what I showed you will only get results for from companies that made orders in both years.

For everything maybe this would work.

SELECT tTable1.sCompanyName, tTable1.Units_Jan01, tTable1.Units_Feb01, tTable1.sCompanyName, tTable2.Units_Jan02, tTable2.Units_Feb02
FROM tTable1, tTable2;