CODEWITHSUNDEEP
mysqlsql
Angel Torres
Angel Torres

Reputation: 67

Display all employee and company names of location with most companies

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.

Upvotes: 1

Views: 5095

Answers (2)

FanoFN
FanoFN

Reputation: 7114

You can do a subquery finding LOCATION_ID with the most companies then use that in WHERE for query that join COMPANY and PEOPLE table.

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

I think the table LOCATION is not necessary at all (as a table or in the query) since it's basically the same thing as COMPANY table; both having matching company and location ID. Unless the LOCATION table is storing unique location ID and with it's own location NAME and you want to show it in the result, perhaps if the LOCATION table is like this:

LOCATION
+----+------------+
| ID |     NAME   |
+----+------------+
| L1 | Location_1 | 
| L2 | Location_2 |
| L3 | Location_3 |  
.....
+----+-----------+

Then : fiddle

Upvotes: 0

nbk
nbk

Reputation: 49375

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

Upvotes: 2

Related Questions