I want to create a table lists that lookup to another table. for example
Table A
| AMOUNT | DESCRIPTION |
|---|---|
| 1000 | i want eat |
| 2000 | i want sleep |
table b(lookup)
| SEARCH | Activity |
|---|---|
| eat | go to eat |
| sleep | go to sleep |
expected result
| Amount | Activity |
|---|---|
| 1000 | go to eat |
| 2000 | go to sleep |
I tried query like this but still didn't work
SELECT amount, IIF(description like '%(select search from table b)%',(select activity from table b), null) as activity
FROM table a
CodePudding user response：
You can use a regular join as well
SELECT a.amount, b.activity
FROM tableA a
JOIN lookupTable b on a.description like '%' b.search '%'
CodePudding user response：
Here is one way to do it, but you'll have to think, as the comments say, how you really want to "parse" A.Description
select A.Amount,B.Activity
from
tableA A
inner join tableB B on B.Search = stuff(A.Description,1,7,'') -- remove first 7 chars