stuck in query..

[techker]

hey guys this is the structure of the Db:

`Ex_ID` int(11) NOT NULL auto_increment,
`N_Etudiant` varchar(25) NOT NULL,
`Professeur` varchar(50) NOT NULL,
`Foyer` varchar(50) NOT NULL,
`Motif` varchar(50) NOT NULL,
`Date` date NOT NULL,
`Notes` text NOT NULL,
`Autre` varchar(150) NOT NULL,

so basically N_Etudiant is a student name..

so when we insert it looks like

`N_Etudiant` Mike
`Professeur` mrpopins
`Foyer` 301
`Motif` fight
`Date` 2011/01/01
`Notes` none
`Autre` 0

so the query i need is to flag if (N_Etudiant) has the same (Professeur) more the 2 times

cause we need to know if the student (N_Etudiant) has been expelled from class by the same teacher (Professeur) more then 2 times..

is this clear?lol

[ppetree]

Off the top of my head I would start with this...

SELECT `Foyer`,
SUM(IF(`Professeur` = "mrpopins", 1,0)) AS `count`
FROM student_table
WHERE `N_Etudiant` = 'Mike'
GROUP BY `N_Etudiant`
ORDER BY count DESC

[smantscheff]

SELECT N_Etudiant, Professeur, count(Professeur) as CP
FROM Foyer
GROUP BY N_Etudiant, Professeur
HAVING CP > 2;

[techker]

this is what i came up with

$query = "SELECT N_Etudiant, Professeur
FROM Expulsion
GROUP BY N_Etudiant, Professeur
HAVING COUNT( * ) >=2
LIMIT 0 , 30";