Counting items

Hi,

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

Cheers.Hi!

Would this one help out?

SELECT appt.ref
, (Case When noteCount > 0 Then 1 Else 0 End) AS notes
FROM touchAppointments appt
, (SELECT appointment
, COUNT(*) as noteCount
FROM touchNotes) note
WHERE appt.practitioner=1
AND appt.ref = note.appointment

Greetings,
Carsten|||I would have writen like this
<code>
SELECT appt.ref , count(*)/count(*) AS notes
FROM touchAppointments as appt inner join touchNotes as note
on appt.ref = note.appointment
WHERE appt.practitioner=1 group by appt.ref
</code>|||You would?

count(*)/count(*) is at best going to return only 1s or Nulls, and at worst would return DivZero errors.

There are serveral ways to do this. CarstenK had one, though it is preferable to use a JOIN rather than linking tables in the WHERE clause.

Here are two more methods:

SELECT touchAppointments.ref, cast(count(touchNotes.appointment) as bit) notes
FROM touchAppointments
left outer join touchNotes on touchNotes.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1
GROUP BY touchAppointments.ref

SELECT touchAppointments.ref, isnull(notesSubquery.hasnotes, 0) as notes
FROM touchAppointments
left outer join (select distinct touchNotes.appointment, 1 as hasnotes from touchNotes) notesSubquery
on notesSubquery.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1|||you are right
count(*)/count(*) is at best going to return only 1s
but how come nulls and div by zero error(even at worst case) with inner join on touchAppointments.ref.|||How do you get the "0" paulbrooks wants to get with his CASE (....)?

Carsten|||Blindman,

Your second solution worked the trick. It returns a 1 for true and 0 for false, which is exactly what I needed.

Thanks a lot, guys.

Paul