Results 1 to 5 of 5

Thread: Sql Count

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Question Unanswered: Sql Count

    Hi,

    I've got a database with two tables:

    members
    notes

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    allow me to introduce you to the LEFT OUTER JOIN
    Code:
    select members.firstname
         , count(*) 
      from members
    left outer
      join notes 
        on members.ref = notes.member
       and notes.new = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17

    Talking

    Hi Paul,

    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.

    Bye

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    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.


    Cheers,

    Paul

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    FROM members
    left outer join notes on members.ref = notes.member and notes.new=1
    group by members.firstname
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •