Bobski Bobski - 1 year ago 65
SQL Question

Selected correct text fields with ID's

I have 2 tables in my SQL Server, tblClientData and tblDataRef. tblDataRef is used as my reference table to populate the comboboxes in my .Net application. In my comboboxes I display text, and when saving the record, I save the ID. Here's an example of what the tblDataRef looks like...

ID Name
100 Accounting
101 Finance
102 Marketing
110 Supervisor
111 Shift Supervisor
112 Manager
113 VP

and my tblClientData

IDClient Dept Position Salary
177 101 112 100,000
454 102 113 350,000

What I'm trying to do is as an example:

Select * from TblClientData where clientID = 177

I'll be populating a DataGridView with this data, so when I actually select all this data, I don't want to have ID's (101, 112, etc) but rather I'd like for it to say Marketing, Manger, etc. There's quite a few more fields in tblClientData as well as tblDataRef, they all work the same way (display text - saveID)

Is there a quick way to do this?

Hoping to achieve this on :

IDClient Dept Position Salary
177 Finance Manager 100,000

Answer Source

Try this :

SELECT IDClient,t2.Name Dept,t3.Name Position,Salary 
FROM TblClientData t1 
INNER JOIN tblDataRef t2 ON t1.Dept = t2.ID
INNER JOIN tblDataRef t3 ON t1.Position = t3.ID
WHERE  clientID = 177
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download