how to compare values from the same column but different table

[#1]

Hi,

I have 2 tables, A and B with the same column Fruits. In table A, a fruit may be called banana,apple but in table B it is may be called apple.

For example,

A	B
Banana,Apple	Apple
Orange,Pineapple	Orange
Banana,Pineapple	Orange
Pineapple	Pineapple
Orange	Orange

I want to be able to match the columns together if the column in table A contains whatever is in table B. In this case, I want the first 2 rows to be matched together and show the results of table B. Is there any way to do this?

Thanks!

[#2]

Chan, one more question:

What do you need if two fruits in Table A have records in Table B? For example if Banana & Apple both exists in Table B, do you want associate both rows or first one only?

[#3]

Hi,

I would like to associate both rows as i need to compare the results in both tables.

Thanks!

[jonathandienst]

Something like this, perhaps:

LOAD

  Fruits as OriginalFruits,

  Trim(SubField(Fruits, ',')) as Fruits,

  1 as TableA,

  ...

From A ....

Join

LOAD

  Fruits

  1 as TableB

FROM B ....

[#5]

Hi,

I forgot to mention that not all the data in the first table has a comma separating them. Will your method still work?

Also, is there any other way other than using join? I am loading both tables in different sections to identify them properly