Bobski Bobski - 26 days ago 5
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

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

tblDefinitions
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

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.

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