Complex INNER JOIN Tables

I have a couple tables that I am exporting data from.

I have one table (call it TABLE1) that contains abbreviations instead of actual words.

For example instead of having the full word "New York City", the column would just contain "NYC".

In another table (call it TABLE2) it has all the abbreviations and the actual words.

So in TABLE2 one column name is FieldName, another Abbreviation and another is Value.

So on a web site, to display that actual name I do something like this


Code:


SELECT CityBoxlabel AS City
FROM TABLE1 INNER JOIN
TABLE 2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') = CityBox.[Value]
WHERE CityBox.FieldName = 'City'




This is working great except that some of these columns contain more than one abbreviation such as "NYC,WDC"
which would stand for "New York City" and "Washington DC"
The items that have more than one abbreviation are not being pulled across because my query is looking for an "NYC,WDC" in TABLE2 to INNER JOIN on but it won't be in there. But "NYC" and "WDC" by themselves are in there.

I tried messing with the INNER JOIN statement by saying something like
TABLE2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') IN CityBox.[Value] INNER JOIN

But it wasn't allowing that.
I can write a program to do this, but I would rather keep it in T-SQL.

Hope this wasn't too confusing!
Any help is greatly appreciated!!