CODEWITHSUNDEEP
sqlsql-server
sekharvarma
sekharvarma

Reputation: 1

How to add three tables in SQL Server

this is 2 tables

this is 2 tables

The screenshots show the tables for reference.

My objective is to retrieve min-max-avg salary per state for date 20150131

I tried this code

select 
    s.Descrip, 
    MIN(Salary) AS Minimum_per_state,
    MAX(Salary) AS Maximum_per_state,
    AVG(Salary) AS Average_per_state 
from
    (select 
         Month_ending,
         Emp_id,Salary,
         Descrip
     from 
         Dim_state 
     full join 
         Dim_city ON Dim_state.State_id = Dim_city.State_id  
     full join
         Employee_details ON Dim_city.City_id = Employee_details.City_id 
     where 
         Month_ending = 20150131) s 
GROUP BY 
    Descrip

but I'm getting this output:

description min_sal max_sal  avg_sal
Delhi       30000    34000   32000
maharastra  25000    35000   30000
tamil nadu  35000    50000   42000
Westbengal  25000   50000    37500

The problem is Bangalore and Goa are missing.

May I know which part went wrong?

Upvotes: 0

Views: 99

Answers (3)

Shah Hassan
Shah Hassan

Reputation: 155

Actually you are missing the point. dim_state has PK so that will be at left side while the dim_city has FK ,so that will be at right side so make right join 

  SELECT ds.descip
,min(salary) min_salary
,max(salary) max_salary
,avg(salary) avg_salary
FROM dim_state ds
RIGHT JOIN dim_city dc ON ds.state_id = dc.state_id
FULL JOIN employee_details ed ON ed.city_id = dc.city_id
WHERE Month_ending = 20150131
GROUP BY ds.descip

Upvotes: 1

Penman
Penman

Reputation: 173

The order of joins also matters when you're using FULL, LEFT or RIGHT JOINs. 

SELECT ds.descip
,min(salary) min_salary
,max(salary) max_salary
,avg(salary) avg_salary
 FROM employee_details ed
      LEFT JOIN dim_city dc ON ed.city_id= dc.city_id
      LEFT JOIN dim_state ed ON dc.state_id = dc.state_id
            WHERE Month_ending = 20150131
            GROUP BY ds.descip

Upvotes: 0

Nayak
Nayak

Reputation: 473

What is the result of the inner query you have?

Can you try this directly (just used a left join and didn't use an inner query)?

SELECT ds.descip
    ,min(salary) min_salary
    ,max(salary) max_salary
    ,avg(salary) avg_salary
FROM dim_state ds
LEFT JOIN dim_city dc ON ds.state_id = dc.state_id
LEFT JOIN employee_details ed ON ed.city_id = dc.city_id
WHERE Month_ending = 20150131
GROUP BY ds.descip

Upvotes: 0

Related Questions