Ms sql, select from a table, subquery - how to do it?

Question

SQL

There is a table: EmployesInfo
with columns: emplId, emolName, EmplCar, ChiefId
You need to select only those employees who have an assigned boss, the assigned boss has a car and the boss must have at least 3 dependencies (an employee with emplId is subordinate to the boss) .

Answer 1

SELECT `employer`.* 
    FROM `EmployesInfo` AS `employer`
    RIGHT JOIN `EmployesInfo` AS `chief` ON `employer`.`ChiefId` = `chief`.`emplId`
    RIGHT JOIN (SELECT `ChiefId`, COUNT(`ChiefId`) AS `count` 
                FROM `EmployesInfo`
                GROUP BY `ChiefId`
                HAVING `count` >= 3) AS `sub` ON `employer`.`ChiefId` = `sub`.`ChiefId`
    WHERE `chief`.`EmplCar` IS NOT NULL

Answer 2

I would put cars and chefs in separate tables so that there would be a 3rd form, but in this case it can be done like this:

SELECT emplId
FROM EmployesInfo
WHERE ChiefId IN (
    SELECT emplId
    FROM EmployesInfo
    --Находим шефов
    WHERE emplId IN (
        SELECT ChiefId
        FROM EmployesInfo
        GROUP BY ChiefId
        --Что бы ему подчинялось >= 3 человека
        HAVING count(1) >= 3
        )
      --Уточняем что шеф не нищеброт и у него есть машина
      AND EmplCar IS NOT NULL
    )