I want to write a SQL Query that will determine the company having highest no .of employees, & then display it's employees with previous company

Question

I have a schema that has following two tables

1. People -

+-------+----------------+------------+-----------------+-----------------+
| ID    | NAME           | DOJ        | PREV_COMPANY_ID | CURR_COMPANY_ID |
+-------+----------------+------------+-----------------+-----------------+
| 1795f | ALEX BROWN     | 1973-03-02 | 4e5b            | 123a            |
| 8772  | Chris Mitchell | 2016-06-15 | 4746            | 4e5b            |
| 5e03  | Patt Hobbs     | 1976-09-14 | 4e5b            | 123b            |
+-------+----------------+------------+-----------------+-----------------+

2. Company -

+-------+---------------+
| ID    | NAME          |
+-------+---------------+
| 4746  | Mora-Sullivan |
| 49de6 | Harmon-Miller |
| 4e5b  | Fakesmith     |
+-------+---------------+

I want to write the query to find the following -

1. First Determine the Company/Companies that has highest no.of employees & then display it's employees with previous company.

I have found out the company which has the highest employees using sql query. i.e Fakesmith here.

SELECT PREV_COMPANY_ID, COUNT(*) as count 
FROM PEOPLE 
GROUP BY PREV_COMPANY_ID
ORDER BY count DESC;

But I am unable to buildup any logic to find, the current employers of the highest number of employees with their previous company names. How can I solve this query?

Answer 1

Are you trying something like this:

create table people(
id varchar(9) not null ,
name varchar(50) not null ,
doj date ,
PREV_COMPANY_ID varchar(50),
CURR_COMPANY_ID varchar(50),
Primary key id(`id`)
);

insert into people values ('1795f','ALEX BROWN','1973-03-02','4e5b','123a'),('8772','Chris Mitchell','2016-06-15','4746','4e5b'),('5e03','Patt Hobbs','1976-09-14','4e5b','123b');


create table company(
id varchar(9) not null ,
name varchar(50) not null ,
Primary key id(`id`)
);

insert into company values ('4746','Mora-Sullivan'),('49de6','Harmon-Miller'),('4e5b','Fakesmith');


select p.name,p.CURR_COMPANY_ID,company.name as company_name 
from people p join (select PREV_COMPANY_ID ,count(*) as comp_high_nr from people group by PREV_COMPANY_ID  order by comp_high_nr desc limit 1) as t on p.CURR_COMPANY_ID =t.PREV_COMPANY_ID join company on p.PREV_COMPANY_ID=company.id  ;