Sql Query for

1

In this MySQL challenge, your query should return the information for the employee with the third highest salary. Write a query that will find this employee and return that row, but then replace the DivisionID column with the corresponding DivisionName from the table cb_companydivisions. You should also replace the ManagerID column with the ManagerName if the ID exists in the table and is not NULL.

ascii_company_boss
ascii_company_boss962×188 17.2 KB

SELECT DISTINCT salary

FROM maintable_1YA0N E WHERE

3 =(SELECT COUNT(DISTINCT salary)

FROM maintable_1YA0N WHERE E.salary <= salary)

2

hi

please see below link !!! hope it helps :slight_smile:

3

Hi,

Thanks for the answer ... I have the 3rd Highest Salary but how to solve

eplace the DivisionID column with the corresponding DivisionName from the table cb_companydivisions. You should also replace the ManagerID column with the ManagerName if the ID exists in the table and is not NULL.

Blockquote

SELECT DISTINCT salary

FROM maintable_1YA0N E WHERE

3 =(SELECT COUNT(DISTINCT salary)

FROM maintable_1YA0N WHERE E.salary <= salary)

4

the table or tables
where these columns are DivisionName ManagerName
join on ID Columns and get these

5

Manager Name is self Join

and Division Name is imaginary Table - Will have the DivisionName column corresponding Division ID column

table

6

if manager name is in same table !!
you can just select manager name

imaginary table join on Division Id .. and select Division Name

7

division

Division Table

8

Please provide sample data as proper ddl and dml and not as pictures help us help you

9

join on ID .. take DivisionName

10

/* write your SQL query below */

SELECT maintable_BS852.ID,maintable_BS852.Name,DivisionName,m.Name as ManagerName,maintable_BS852.Salary FROM maintable_BS852

INNER JOIN

cb_companydivisions as i on i.ID = maintable_BS852.DivisionID

INNER JOIN

maintable_BS852 as m on m.ID = maintable_BS852.ManagerID

ORDER BY Salary

DESC LIMIT 1

OFFSET 1

1 Like
11

Hello Mo,
You are a life saver but would like to give some explanation of this please,

SELECT a.ID,a.Name,DivisionName,a.Name as ManagerName,a.Salary
FROM maintable_4EMTN as a
INNER JOIN cb_companydivisions as b
ON a.DivisionID=b.ID
ORDER BY Salary DESC
LIMIT 1
OFFSET 2

If i type this i get the correct result but with a wrong manager name (which is the same as the name of the employee instead of his manager)?Can you tell me what is your 2nd inner join doing exactly cuz i am a bit confused
Thanks in advance!

12

hi

hope this helps

1 Like
14

I spent 1 hour on this, I hope i'm the last one

SELECT DISTINCT E.ID, E.Name, DivisionName,m.Name as ManagerName,

E.salary FROM maintable_SEAHL E

inner join

cb_companydivisions as i on i.ID = E.DivisionID

inner join

maintable_SEAHL as m on m.ID = E.ManagerID

where
3=(SELECT COUNT(DISTINCT SALARY)
FROM maintable_SEAHL Where E.salary <= salary)