Getting SQL Error "every derived table must have its own alias sql"

Question

How do I fix this query -

SELECT People.Name, Company.Name 
FROM People 
JOIN Company ON People.Current_Company_ID = Company.ID
WHERE Current_Company_ID IN
(SELECT Previous_Company_ID FROM People GROUP BY Previous_Company_ID HAVING Count(Previous_Company_ID) = (SELECT MAX(C) FROM (SELECT COUNT(Previous_Company_Id) AS C FROM People
GROUP BY Previous_Company_Id
ORDER BY COUNT(Previous_Company_Id) DESC)));

People Table has four columns

id, name, previous_company_name, current_company_name

Company Table has two columns

id, name

Format.. make it easy to show the problem to others. – user2864740 Jan 19 '20 at 04:17 — user2864740, Jan 19 '20 at 04:17

score 0 · Answer 1 · answered Jan 19 '20 at 04:22

Replace

    (SELECT COUNT(Previous_Company_Id) AS C 
FROM People GROUP BY Previous_Company_Id ORDER BY COUNT(Previous_Company_Id) DESC)

with

    (SELECT COUNT(Previous_Company_Id) AS C 
FROM People GROUP BY Previous_Company_Id ORDER BY COUNT(Previous_Company_Id) DESC) AA

You are missing an alias there.

You new query is:

SELECT People.Name, Company.Name 
FROM People JOIN Company ON People.Current_Company_ID = Company.ID WHERE Current_Company_ID IN (SELECT Previous_Company_ID 
        FROM People GROUP BY Previous_Company_ID HAVING Count(Previous_Company_ID) = (SELECT MAX(C) FROM 
(
    SELECT COUNT(Previous_Company_Id) AS C 
    FROM People GROUP BY Previous_Company_Id ORDER BY COUNT(Previous_Company_Id) 
    DESC
)AA
));

score 0 · Answer 2 · answered Jan 19 '20 at 04:25

Please try below query.

SELECT P.Name, C.Name 
FROM People AS P
JOIN Company AS C ON P.Current_Company_ID = C.ID
WHERE Current_Company_ID IN
(SELECT Previous_Company_ID FROM P 
GROUP BY Previous_Company_ID 
HAVING Count(Previous_Company_ID) = (SELECT COUNT(Previous_Company_Id) AS Cnt FROM P);

Getting SQL Error "every derived table must have its own alias sql"

2 Answers2