Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Hierarchical Lookup and Find Non Null Expressions In Sql Server

When testing database, sometimes, we need to look up information from multiple tables and find non null values. We can use join and COALESCE statement.

 

In this example below, we have a master table that holds employee_id and name, and other 3 tables that contain employee_id and salary information. We need to find total salary of each employee. Below is the Sql script for that. 

 

Use AdventureWorks2008
Go

create table employee_info(
employee_id tinyint identity,
name varchar(50) not null
constraint employee_id_pk Primary Key(employee_id))
Go

create table hourly_wage_info(employee_id tinyint,
hourly_wage decimal null)


create table monthly_salary_info(employee_id tinyint,
monthly_salary decimal null)

create table weekly_salary_info(employee_id tinyint,
bi_weekly_salary decimal null)

Insert into employee_info values('Adam'), ('Smith'),('Jack'), ('Mike'), ('Steve'), ('Angel'), ('Summer')
Insert into hourly_wage_info values(1,25),(3,30)
Insert into monthly_salary_info values(2,5000),(4,4000)
Insert into weekly_salary_info values(5,1500),(6,1700),(7,2200)

select e.employee_id,e.name, CAST(COALESCE(hourly_wage*40*52,monthly_salary*12, bi_weekly_salary*52) AS money)
 as 'Total Salary' from employee_info e left join hourly_wage_info h
on e.employee_id=h.employee_id left join monthly_salary_info m
on e.employee_id=m.employee_id left join weekly_salary_info w on 
e.employee_id=w.employee_id

SQL Result

employee_id name                                               Total Salary
----------- -------------------------------------------------- ---------------------
1           Adam                                               52000.00
2           Smith                                              60000.00
3           Jack                                               62400.00
4           Mike                                               48000.00
5           Steve                                              78000.00
6           Angel                                              88400.00
7           Summer                                             114400.00

(7 row(s) affected)