how to compare one field of table to another fields of table

[#1]

hi,

how to compare one filed values one table to another table fields value and that common values store in to one separate column?

sample data:

table1:

eid   name  department

101 ram       d1

102 shyam  d2

103 rgghu   d6

104    dgjk  d7

table2:

d1          d2      d3    d4     d5     d6

shyam  ram    siva  guru   dgik   kima

rgghu   sukku   dsk  frum  idhd   shiva

now i want to compare name in table1 with d1,d2,d3,d4,d5,and d6 if we find same name in in d1 that will be display req department as d1,

if not in d1 that will go to d2 and that will store in to req department and so on if not found any department display like no match.

thanks

raju

[marcus_sommer]

I think you will need to transform your second table with a The Crosstable Load and this could be joined or mapped to the first table and then you could compare both fields. Whereby the logic for the comparing isn't quite clear for me - if you beside the (join/maaping) matching need to look into another records you need Peek() or Previous() ?

- Marcus