sql with count [modified]

[foryou]

Hi!! I have the following database : id_student-------id_module-----id_validat-------years 1000--------------11------------2----------------2006 1000---------------11-----------2-----------------2007 2000---------------11-----------3----------------2006 2000---------------12-----------1-----------------2007 3000---------------11------------2----------------2009 3000---------------11------------3-----------------2008 I try to show id_student who id_module=11 and id_validat = 2For two different years in this example the student who id_student=1000 verefier the condition. but my sql is false

SELECT COUNT(*) AS Expr1, id_student,years
FROM T1
WHERE (id_MODULE=11 )AND (id_validat= 2)
GROUP BY id_student,years
HAVING (COUNT(*) = 2)

Can you help me? Thanks

modified on Wednesday, February 4, 2009 8:50 AM

[Blue_Boy]

SELECT COUNT(idstudent) AS IDStudentCount, idstudent, (SELECT TOP (1) t1.years FROM myTable AS t1 WHERE (t1.idstudent = myTable.idstudent)) AS Years FROM myTable WHERE (idvalidat = 2) AND (idmodule = 11) GROUP BY idstudent HAVING (COUNT(idstudent) = 2)

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

[Wendelius]

Your query was very close. Just don't group by on years column:

SELECT id_student,
COUNT(*) as years
FROM T1
WHERE (id_MODULE=11 )
AND (id_validat= 2)
GROUP BY id_student
HAVING (COUNT(*) = 2)

The need to optimize rises from a bad design.My articles[^]