Take the 2-minute tour ×

I have a query but I dont know how to make it optimal. I have three objects.

  • SCP
  • Question : it has a field that points SCP (SCP_id)
  • Answer : it has a field that points Question (question_id)

How can I make a query that counts the number of answer within a given SCP

share|improve this question

1 Answer 1

up vote 1 down vote accepted

What about this:

select count(a) 
from Answer a 
where a.question.scp.id = :id

Which generates the following SQL:

  count(answer0_.id) as col_0_0_ 
  Answer answer0_,
  Question question1_ 
  and question1_.scp_id=?

Seems pretty efficient.

share|improve this answer
Thanks, that works. Would it be more efficient to use denormalization ? –  Kenshiro92 Sep 30 '10 at 15:56
@user284237 The query is doing one join, I wouldn't bother. –  Pascal Thivent Sep 30 '10 at 17:28

Your Answer


