Display all employee and company names of location with most companies-CodePudding

Recently I received this question on a test and I cannot reach a solution even after trying it on my own after the test was done.

I have 3 tables:

PEOPLE
 ---- ------- ---------- 
| ID |  NAME |COMPANY_ID|
 ------------ ---------- 
| 1  |George |    C1    | 
| 2  | Jim   |    C2    |
| 3  | John  |    C3    |  
 ---- ------- ---------- 

COMPANY
 ---------------- ----------- 
| ID |    NAME   |LOCATION_ID|
 ---------------- ----------- 
| C1 |Water-Based|    L1     | 
| C2 |Gas-Giant  |    L1     |
| C3 |Fire-Proof |    L2     |  
 ---- ----------- ----------- 

LOCATION
 ---- ----------- 
| ID | COMPANY_ID|
 ---- ----------- 
| L1 |     C1    | 
| L1 |     C2    |
| L2 |     C3    |  
 ---- -----------

The goal is to determine which location has the most companies, then, display the name of the employees for the companies on that location as well as the location they work in.

I have tried to do a subquery where I count the number of times a location occurs within the COMPANY table and then display the people that work there as well as the company, but I can't seem to get it right, this is my code:

SELECT PEOPLE.NAME AS EMPLOYEE, t.NAME AS COMPANY
FROM(SELECT *,COUNT(LOCATION_ID) OVER(PARTITION BY LOCATION_ID) AS LCOUNT FROM COMPANY) AS t
JOIN PEOPLE
ON PEOPLE.COMPANY_ID = t.ID;

EXPECTED RESULT:

 ------ ----------- 
| NAME |  COMPANY  |
 ------------------ 
|George|Water-Based| 
|Jim   |Gas-Giant  |  
 ------ -----------

Any help and advice are highly appreciated, thanks.

CodePudding user response：

This approach will first find all location that have the lost companys,even when there are many and then diplay all employees with their company

SELECT P.NAME AS namy,
       C.NAME AS company
FROM COMPANY C
JOIN PEOPLE P ON C.ID=P.COMPANY_ID
WHERE LOCATION_ID IN(
SELECT 
`ID` 
FROM LOCATION
 GROUP BY `ID`
 HAVING COUNT(*) 
 = (SELECT   COUNT(*)  FROM LOCATION GROUP BY `ID` ORDEr By COUNT(*) DESC LIMIT 1))

PEOPLE_NAME | COMPANY_NAME
:---------- | :-----------
George      | Water-Based 
Jim         | Gas-Giant

db<>fiddle here

CodePudding user response：

You can just do a JOIN with PEOPLE table then use EXISTS function to check LOCATION_ID with most companies. Try this:

SELECT P.NAME AS PEOPLE_NAME,
       C1.NAME AS COMPANY_NAME
FROM COMPANY C1
JOIN PEOPLE P ON C1.ID=P.COMPANY_ID
  WHERE C1.LOCATION_ID=(SELECT LOCATION_ID
                 FROM COMPANY C2 
                 GROUP BY C2.LOCATION_ID
                ORDER BY COUNT(ID) DESC LIMIT 1);

Demo fiddle