I have these tables:

CREATE TABLE Financial_plan 
(
    ID INT NOT NULL,
    advisor_ID INT NOT NULL,
    product_ID INT NOT NULL,
    price INT NOT NULL,

    PRIMARY KEY (ID)
);

CREATE TABLE Advisor 
( 
    ID INT NOT NULL,  
    role_id INT NOT NULL,  
    office_id INT NOT NULL,  
    name VARCHAR (50),  
    surname VARCHAR (50) NOT NULL,  
    active NUMERIC (1) NOT NULL,  

    PRIMARY KEY (ID)  
);

And I do a new column count_plan like this

SELECT 
    Advisor_ID, count_plan 
FROM 
    (SELECT 
         Financial_plan.Advisor_ID, 
         COUNT(Financial_plan.ID) AS count_plan
     FROM 
         Financial_plan
     GROUP BY 
         Financial_plan.Advisor_ID
     ORDER BY 
         count_plan DESC)

I would like to JOIN a new table with column count_plan with Advisor table, but can't figure it out

I try

SELECT 
    Advisor_ID, 
    count_plan 
FROM 
    (SELECT 
         Financial_plan.Advisor_ID, 
         COUNT(Financial_plan.ID) AS count_plan
     FROM 
         Financial_plan
     GROUP BY 
         Financial_plan.Advisor_ID
     ORDER BY 
         count_plan DESC) Advisor_plan
LEFT JOIN 
    Advisor ON Advisor_plan.Advisor_ID = Advisor.ID

This statement doesnt do the join.

I would like to add to information from Advisor table to record of the best advisors by number of plans.

So I would like to get table like this

Advisor_ID count_plan role_id office_id name surname active

CodePudding user response：

As every advisor_id is unique, you have a 1:! relation to advisor, so you need to add only the missing columns

SELECT 
    Advisor_ID, 
    count_plan,
    role_id,
    office_id,
    name,
    surname,
    active
FROM 
    (SELECT 
         Financial_plan.Advisor_ID, 
         COUNT(Financial_plan.ID) AS count_plan
     FROM 
         Financial_plan
     GROUP BY 
         Financial_plan.Advisor_ID
     ORDER BY 
         count_plan DESC) Advisor_plan
LEFT JOIN 
    Advisor ON Advisor_plan.Advisor_ID = Advisor.ID

Advisor_ID | count_plan | role_id | office_id | name | surname | active
---------: | ---------: | ------: | --------: | :--- | :------ | -----:

db<>fiddle here