Find employees who have worked on at least 2 projects-CodePudding

I need to make basically this without using the "Group by"

SELECT  idEmploye , COUNT(*) AS nbrProjet
FROM ressourcesprojet_ 
GROUP BY idEmploye 
HAVING COUNT(*) > 1;

My Database code for creating it : https://sourceb.in/JePvGUccpU

Things i tried :

SELECT e.idEtape, e.idProjet, e.dateDebut, e.dateFin 
FROM (
    SELECT idProjet, dateDebut, dateFin 
    FROM Projet) AS p 
RIGHT JOIN EtapexProjet AS e 
    ON e.dateDebut > p.dateDebut AND e.dateFin > p.dateFin 
ORDER BY p.idProjet;

SELECT  E.idEmploye ,
    (SELECT COUNT(idProjet) 
     FROM ressourcesprojet_ 
     Where E.idEmploye = idEmploye) AS nbrProjet 
From employe_ E;

SELECT idEmploye 
FROM ressourcesprojet_ WHERE 1 < (
    SELECT COUNT(idProjet) 
    FROM ressourcesprojet_ 
    where idEmploye = idEmploye);

I just can't wrap my head around it

CodePudding user response：

You can use this:

SELECT DISTINCT p1.idEmploye
FROM RessourcesProjet_ AS p1
JOIN RessourcesProjet_ AS p2 ON p1.idEmploye = p2.idEmploye AND p1.idProjet != p2.idProjet

This joins the RessourcesProjet_ with itself to find all pairs of rows with the same employee and different projects, so this will find anyone who has worked on more than one project.

Then it uses DISTINCT so we don't see all the duplications.

This doesn't generalize easily to "at least N" like the methods that use COUNT() do. It needs to self-join N copies of the table, and all the ON clauses will need to check that the project isn't in any of the previous copies of the table.

CodePudding user response：

The reason why you want, would affect if this is usable. Here is an example, which technically doesn't use the grouping, but if you added any other columns would easily require it:

DECLARE @test2 TABLE (num INT)

INSERT INTO @test2 VALUES (1), (2), (3), (2), (4)

SELECT DISTINCT *
FROM @Test2 t
WHERE (SELECT COUNT(*) FROM @Test2 it WHERE it.num = t.num) > 1

P.S - I have to assume this is either a puzzle or test question

Alternative Edit:

DECLARE @test2 TABLE (id INT, num INT)

INSERT INTO @test2 VALUES (1, 1), (2, 1), (2, 3), (3, 2), (3, 4)

SELECT *
FROM @Test2 t
WHERE NOT EXISTS (SELECT * FROM @Test2 it WHERE it.id = t.id AND it.num <> t.num)

This results in the following being returned:

id, num
1, 1