CODEWITHSUNDEEP
sqlsql-server
Sora lin
Sora lin

Reputation: 53

SQL Server : mapping values from one table to another

I'm working on finger prints device reader in which the device (by itself) generates a sort of "keys" for each personnel. I take these keys and store them for each known employee I have in table EmpInfo_Device with two columns:

  1. DevKey (which is the key generated by the FP reader)
  2. Emp_id (which is a foreign key from another table EmpInfo for the specific employee in which the key is generated for)

(e.g. the device generated a key (40) for employee "Jack" with id (120)). sometimes there are certain individuals who are not employees but still use the device, thus generating a key for them. The problem is that I want to get all the log entries from another table LogData_Stage1 which consists of several columns mainly:

  1. DevKey (as mentioned before)
  2. LogDateTime
  3. LogType

Now, I want all the log entries from LogData_Stage1 for the stored employees only in EmpInfo_Device.

I have tried this query but I feel it's somehow wrong.

Select *
From EmpInfo_Device e
Where e.DevKey in (Select lg.DevKey from LogData_Stage1 lg)

This query returns all the values in EmpInfo_Device - however, the number of distinct IDs in LogData_Stage1 happens to be 86, so I'm a bit confused.

Thanks in advance and I'm sorry if the question was too long

Upvotes: 1

Views: 2943

Answers (1)

sticky bit
sticky bit

Reputation: 37472

If I understand this right, you want all the log entries, that were made for an employee (and not those for other, non employee users of the reader) along with the ID of the employees. For that, you can use an INNER JOIN.

SELECT ed.emp_id,
       l.logdatetime,
       l.logtype
       FROM logdata_stage1 l
            INNER JOIN empinfo_device ed
                       ON ed.devkey = l.devkey;

Upvotes: 1

Related Questions