Bobski Bobski - 1 month ago 6
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...

tblDataRef
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

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