Posts Tagged ‘Self Join’

The Ultimate Correlated SQL Query

Posted: August 23, 2013 by Transformer in MS Access, SQL
Tags: , ,

Problem Statement:  There is a table named SkillSet which has employees’ skill details. We need to find out employees (skill wise) who have the highest proficiency rating. If multiple employees have the highest proficiency rating in some skill then the employee having more experience should be selected.

Employee Skills Proficiency Experience
Thor SQL 9 1
Thor VBA 8 1
Mike VBA 9 1
Mike SQL 6 1
Robert VBA 9 2
Robert C# 6 2
Robert SQL 9 1
Logan C# 9 1
Logan SQL 9 2

Solution:Lets do it in steps. Try to find out skill wise employees having maximum proficiency. (more…)

Advertisements

Calculating Ratio Within Groups using Self Join

Posted: May 21, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is table named Employee that has department wise employee details as given below:


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

We have to calculate ratio between male and female employees in each department.
Solution: This can be done using a self join query where we calculate gender wise population in one query and department wise population in the other and join them as below:

SELECT T1.Dept, T1.Gender,T1.Pop/T2.Pop
    FROM
            (SELECT Dept,Gender,count(*)  as Pop
                  FROM  
                        Employee  Group By Dept,Gender) as T1

                       INNER JOIN

             (SELECT Dept,Count(*) as Pop
                   FROM
                       Employee  Group By Dept) as T2

 ON T1.Dept = T2.Dept

Use of Self Join in Update Query

Posted: May 3, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Some times one might need to update a record in a table based on a calculation on some field of the same table.It can not be done without using Self Join.
e.g. Consider the following data, there is a region named Region1 and this region includes C1 ,C2 and C3 cities.
So the population of Region1 is equal to the sum of population of these three cities.Now there are two more cities C4 and C5 that needs to be added in Region1.
Now population of Region1 should be updated by adding the population of C4 and C5 into it.


Geography	Population
C1	         136376
C2	         154644
C3	         176040
C4	         148793
C5	         138288
C6	         137754
C7	         141093
Region1	         467060
Region2	         278847

(more…)

RANK in MS Access

Posted: April 23, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

However same result can be achieved using a correlated query.

e.g. Consider a dataset of student marks below.

Students Marks
Tony 34
Bob 32
Thor 48
Jack 42
Tom 41
Kate 45
Sid 26

Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks)
          FROM
                 [Table] AS T1
         WHERE T1.Marks >= T2.Marks) AS Rank
FROM
      [Table] AS T2
ORDER BY Marks DESC

Following is the result of the query.

Students Rank
Thor 1
Kate 2
Jack 3
Tom 4
Tony 5
Bob 6
Sid 7

If,  rank 1 is to be assigned to the lowest value, then in that case the query above can be slightly modified to:

SELECT Students,
       (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks <= T2.Marks) AS Rank 
FROM 
      [Table] AS T2 
ORDER BY Marks