Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Right, i have the below query -
code:
SELECT
`teacher_ref_cv`.`Teacher_ID`,
COUNT(`teacher_ref_cv`.`Teacher_ID`) AS `Num_of_Ref`
FROM `teacher_ref_cv`
WHERE `teacher_ref_cv`.`Type` = 'Reference'
GROUP BY `teacher_ref_cv`.`Teacher_ID`
which throws out (etc) -
code:
Teacher_ID----Num_of_Ref
___12____----____2____
___17____----____1____
___543___----____5____
___298___----____3____
Problem is i only want to pull out Teacher_ID's where Num_of_Ref is bigger equal-to 2 and you can't use column aliases in the WHERE clauses and you can't have HAVING and WHERE clauses together.
Has anyone got any ideas how i can achieve this?
I'm guessing subquery the COUNT? Only issue i have with this is that this query is already a subquery (it's getting extremely messy very quickly  ) and there could be performance issues.
[Edited on 29-03-2011 by Dom]
|
gooner_47
Member
Registered: 20th Jul 04
Location: Bexhill/Croydon
User status: Offline
|
Don't know if this will be any different to SQL Server, I presume not - but the following works for me:
code:
SELECT 'teacher_ref_cv'.'Teacher_ID'
, COUNT('teacher_ref_cv'.'Teacher_ID') AS 'Num_of_Ref'
FROM 'teacher_ref_cv'
WHERE 'teacher_ref_cv'.type = 'Reference'
GROUP BY 'teacher_ref_cv'.'Teacher_ID'
HAVING COUNT('teacher_ref_cv'.'Teacher_ID') > = 2
[Edited on 29-03-2011 by gooner_47]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Missed that one Cheers dude 
|
