Bobski Bobski - 9 months ago 35
SQL Question

Select text values instead of ID's Inner Joining another table

I have two tables right now that I'm trying to inner join to get some text values. I have a field where I save data. Users are able to select data form comboboxes/textboxes and then it saves - but the values I save from comboboxes are ID's rather than text values. Now I'm trying to join the tables and grab the text values instead of ID's to be displayed in a datagridview.

Here's what my tables look like

OrigValue NewValue FieldName
12 13 Dept
17 18 Position
9 10 Cost
Samm Sammy Name

ID Name Field
9 Microsoft Seller
10 Adobe Seller
12 Finance Dept
13 Research Dept
17 Manager Position
18 Entry Position

I'm trying to....

Select * from tbl1 and inner Join tblDefitions to get text values

The problem is that if you look at the 3rd record in tbl1 it shows 9 and 10 which can be referenced in tblDefinitions but the field name is not in tblDefinitions. I'm trying to only JOIN the two tables where the field names match, and display everything else as is.

Desired end result....

OrigValue NewValue FieldName
Finance Resarch Dept
Manager Entry Position
9 10 Cost

Answer Source

You'd have to join the tblDefinitions twice. Outer join, as there may be missing matches. Then use COALESCEto either display the found value or the default.

  coalesce(, cast(t1.origvalue as varchar)) as origvalue,
  coalesce(, cast(t1.newvalue as varchar)) as newvalue,
from tbl1 t1
left join tbldefinitions ov on t1.origvalue = and t1.fieldname = ov.field
left join tbldefinitions nv on t1.newvalue = and t1.fieldname = nv.field;