var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Sql Count
I've got a database with two tables:
Each member can have zero or more notes associated with them and each note has a 'new' field which indicates that it hasn't been read since it was added.
I want to produce a list of members and highlight the ones in bold that have unread notes.
If I use
SELECT members.firstname, COUNT(*) FROM members, notes WHERE notes.member=members.ref AND notes.new=1
then I only get the items that have unread notes (and I don't actually need to know the count).
How can I get what I want?
allow me to introduce you to the LEFT OUTER JOIN
on members.ref = notes.member
and notes.new = 1
If I understand what you want to do you can use this form ....
SELECT members.firstname, COUNT(*)
FROM members, notes
WHERE notes.member=members.ref AND notes.new=1
SELECT *,(Case When Match > 0 Then 1 Else 0 End) as HighLight FROM Members
INNER JOIN ( SELECT member, COUNT(*) as Match FROM Notes GROUP BY member) Notes ON Notes.member=Members.ref
I don't test it but the logic should be right...
Let me know.
Thanks to both.
It was a combination of your answers that solved it.
R937 solved main issue (I've never used the CASE structure before), but the LEFT JOIN was also needed to ensure the members without notes were also returned.
You shouldn't need the CASE statement. Rudy's left outer join should be sufficient.
This returns 1 if the member has new notes, and 0 if they do not:
SELECT members.firstname, cast(count(note.member) as bit) new
left outer join notes on members.ref = notes.member and notes.new=1
group by members.firstname