Impromptu Tables

[cogdev]

I have 2 tables:

Tables 1

Xlate - this is a translation table
Action Code Translate
1 Applied
2 Graduated
3 Withdrawn, etc
4
.
.
The second Table
Actions Code
1
2
3
4
5
...
I would like to translate the action code from the second table to the translate in the first table. Any thoughts?

 

[GJParker]

May be I am not seeing the problem here.

Just join the 2 tables together

SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Action Code = Table2.Actions Codes


Gary Parker
MIS Data Analyst
Manchester, England

 

[griffindm]

Why not a simple catalog join?

 

[cogdev]

Sorry I said that incorrectly.

Here is the scenario:

Xlate - this is a translation table
Action Code Translate
1 Applied
2 Graduated
3 Withdrawn, etc
4
.

Table 2
Action code 1 Action code 2 Action Code 3

1 2 3
3 1 4


etc.

And I want to oputput
Action 1 = applied
Action 2 = Graduated
Action 3 = withdrawn etc.

 

[drlex]

It sounds like you should alias the tables and join on the relevant fields.
In catalogue, tables, add the translation table twice more as aliases Alias Xlate1 and AliasXlate2 (for example)
In joins,
make an equi-join between Table2.Action Code 1 and Xlate.Action Code
make same join between Table2.Action Code 2 and AliasXlate1.Action Code
make same join n between Table2.Action Code 3 and AliasXlate2.Action Code


soi la, soi carre

 

[cogdev]

Thanks a million, I thinks its working.
I just need to tune my joins a bit now.

I may need to make a few outer joins - example where action code 1 is missing, but I still need to have that record show?

 

[drlex]

Not a problem; just put a tick in the 'outer join' box on the table2 side of the join and you'll get all table2 records.


soi la, soi carre