Join multiple tables and then lookup

[sw1085]

My data is layed out in three tables as follows:

TableCo1

DATE ACC_CODE AMOUNT DESCRIPTN
20120801 412 100.00 Test entry


TableCo2

DATE ACC_CODE AMOUNT DESCRIPTN
20120801 412 100.00 Test entry

RefTable1
CO_CODE ACC_CODE ACC_NAME
Co1 412 Buildings
Co2 412 Cleaning

I need to join table 1 and 2 and add a new column called "COMPANY" and Co1 and Co2 in the field but I also want to join ACC_NAME as well.

So far I have this statement working to join the Table1 and Table2

select *, 'Co1' AS COMPANY
from TableCo1
UNION ALL
select *, 'Co2' AS COMPANY
from TableCo2

I just need the above code amended so that the follow appears in one query:

ConsolTable

DATE ACC_CODE AMOUNT DESCRIPTN ACC_NAME COMPANY
20120801 412 100.00 Test entry Building Services Co1
20120801 412 100.00 Test entry Cleaning Co2

I hope someone can understand what I am trying to achieve.

Best regards

SW

 

[pr2-eugin]

Hi it is a bit hard to see the examples you have provided.. Could you EDIT your post and wrap them either in QUOTE or CODE tags?? So we can properly look into it?

 

[sw1085]

Thanks, sorry about that.

I hope it is readable now.

Hi it is a bit hard to see the examples you have provided.. Could you EDIT your post and wrap them either in QUOTE or CODE tags?? So we can properly look into it?

 

[sw1085]

Can anyone help me solve my problem? I guess I need some kind of join but not sure which one or how to add to my existing query.

Thanks very much

SW

 

[pr2-eugin]

Hi Sorry about that.. I was hoping the edit was a bit better.. anyway.. I created a dummy table and queries and tried to replicate yours.. So as you guessed it needs JOIN not Union.. so your Query becomes..

SELECT TableCo1.*, RefTable1.CO_CODE AS COMPANY, RefTable1.ACC_NAME
FROM TableCo1 LEFT JOIN RefTable1 ON TableCo1.ACC_CODE = RefTable1.ACC_CODE;

Wht I do not understand is why do you have the exact data of TableCol1 in TableCo2?? You only needed the one table... I got the following as result..

DateAcc    ACC_CODE    AMOUNT    DESCRIPTN    COMPANY    ACC_NAME
14/08/2012    412    £100.00    Test    Col1    Buliding
14/08/2012    412    £100.00    Test    Col2    Cleaning