CODEWITHSUNDEEP
sqljoincase-statement
user3491260
user3491260

Reputation: 1

SQL Results: Employee Name and Location with Case Statement

In SQL...How can I match the building name from one table, with the employee id of another? When I run a query, like below, the building name is not displaying in the results. It is leaving it blank, as stated in the Case statement of the script. I don't think this should be happening. I have attached an image of table examples and desired results.

Example

SELECT DISTINCT
emp.[emp_ln],
emp.[emp_fn],
empb.[emp_id],
'BUILDING_NAME' = CASE WHEN emp.[emp_id] = empb.[emp_id]                                             
                       THEN bld.[bld_name] ELSE ''
                                    END

FROM Employees as emp 
JOIN [dbo].[empbld] as empb on empb.[emp_id] = emp.[emp_id]
JOIN [dbo].[bl] as bld on bld.[bld_num] = empb.[bld_num]

Where emp.[emp_status] = ‘A’

Group by 
emp.[emp_ln],
emp.[emp_fn],
empb.[emp_id],
bld.[bld_name]

Upvotes: 0

Views: 341

Answers (1)

D-Shih
D-Shih

Reputation: 46249

There isn't any aggregate function so you don't need to use group by clause.

From your expect result and table schema,I think you don't need use case when

Because CASE WHEN condition emp.[emp_id] = empb.[emp_id] will be always equal from the join statement 

JOIN [dbo].[empbld] as empb on empb.[emp_id] = emp.[emp_id]

You can get bld_name from bl table directly.

SELECT 
  emp.[emp_ln],
  emp.[emp_fn],
  empb.[emp_id],
  bld.bld_name
FROM Employees as emp 
  JOIN [dbo].[empbld] as empb on empb.[emp_id] = emp.[emp_id]
  JOIN [dbo].[bl] as bld on bld.[bld_num] = empb.[bld_num]
Where emp.[emp_status] = ‘A’

sqlfiddle:http://sqlfiddle.com/#!18/a3979/1

Upvotes: 1

Related Questions