How to check if there exist only one record for a certain Id

Question

How to check if there exist only one record for a certain Id

I have two tables called Tbl_Company and Tbl_Employee I am fetching employees as follows-

SELECT DISTINCT emp.employee_id
FROM Tbl_Company comp
           , Tbl_Employee emp
WHERE 
           emp.company_id = comp.company_id 
           AND emp.company_id = 1234;

This query returns exactly one value. How can I make sure that above query returns exacly one value for any comany_id I enter.

I tried using solutions given in This post with no success. Is there any simpler way to do this.

Answer 1

I have solved this by using ans by @davegreen100 in comment

SELECT comp.companyid, count(distinct emp.employee_id), 
FROM Tbl_Company comp
           , Tbl_Employee emp
WHERE 
           emp.company_id = comp.company_id 
           AND emp.company_id = 1234
GROUP BY comp.companyid

This will give me the count of employees per company

Answer 2

this would return one row per company

SELECT comp.companyid, max(emp.employee_id) lastEmployeeID
FROM Tbl_Company comp
           , Tbl_Employee emp
WHERE 
           emp.company_id = comp.company_id 
           AND emp.company_id = 1234
GROUP BY comp.companyid

Answer 3

the following query is simple and flexible. it will return a list of all employees which are alone in their companies (returned with full employee information).

you can check if a defined company has one lonely employee by enable condition about company or you can check if employee is a lonely employee by enabling employee condition.

SELECT emp.*
FROM Tbl_Company comp
           /*, Tbl_Employee emp*/
WHERE (emp.company_id , 1) in (select t.company_id, count(t.employee_id) from Tbl_Company t )
  --AND emp.company_id = 1111 /*filter conditions on company_id*/
  --AND emp.employee_id = 1234/*filter conditions on employee_id*/;