Select value from 2 tables with condition-CodePudding

I want to select values Employee ID, employee first name, project name from tables employee and project of the employee assigned to more than one project.

Thanks everybody I can solve them and sorry about this question is not clear

CodePudding user response：

You must use a JOIN

select EmplyoeeID, FirstName, ProjectName 
from EmployeeDetail 
join ProjectDetail on EmplyoeeDetailID = EmployeeID
where (select count(*) from ProjectDetail where EmplyoeeDetailID = EmployeeID) > 1

Please read more about joins here: https://www.w3schools.com/sql/sql_join.asp

CodePudding user response：

Try this (a simple INNER JOIN)

SELECT e.EmployeeID, e.firstName, p.ProjectName
FROM Employee e
JOIN ProjectDetail p
ON e.EmployeeID = p.EmployeeDetailID

If you want only employees with more than 1 project

SELECT e.EmployeeID, e.firstName, p.ProjectName
FROM Employee e
JOIN ProjectDetail p
ON e.EmployeeID = p.EmployeeDetailID
WHERE 
    (SELECT COUNT(1) FROM ProjectDetail p2
    WHERE p2.EmployeeDetailID = p.EmployeeDetailID) > 1

CodePudding user response：

A window function is probably the most efficient

SELECT
  e.EmployeeID,
  e.firstName,
  p.ProjectName
FROM Employee e
JOIN (
    SELECT p.*,
      count = COUNT(*) OVER (PARTITION BY p.EmployeeDetailID)
    FROM ProjectDetail p
) p ON e.EmployeeID = p.EmployeeDetailID AND p.count > 1;