CODEWITHSUNDEEP
sqlsql-serverjoin
BobSki
BobSki

Reputation: 1552

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

Upvotes: 2

Views: 1919

Answers (3)

Tarique Ahmed
Tarique Ahmed

Reputation: 691

You not joining 'tblDefinitions' twice with both new and old value with id column to get its text value. 

select 
        isnull(tdo.Name,cast(t1.OrigValue as varchar)) OrigValue         
       ,isnull(tdn.Name,cast(t1.NewValue as varchar)) NewValue
       ,t1.FieldName 
 from
    tbl1 t1
    left join tblDefinitions tdo on  t1.FieldName = tdo.Field and t1.OrigValue = tdo.ID
    left join tblDefinitions tdn on t1.FieldName = tdn.Field and t1.NewValue = tdn.Id

Upvotes: 0

Takarii
Takarii

Reputation: 1648

This will match up your keys such that it shows you what the original Name was, and the new one. 

SELECT tblDefinitions.Name AS 'Original Value', t.Name AS 'New Value', FieldName
FROM tbl1 
JOIN tblDefinitions ON tbl1.OrigValue = tblDefinitions.ID 
JOIN tblDefinitions t ON tbl1.NewValue = t.ID

However, I don't understand the relation between FieldName in tbl1 and Field in tblDefinitions.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

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;

Upvotes: 1

Related Questions