Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Help in joining three tables for COUNT

    Hi,

    I have three tables - clients, messages, orders. clientid is the primary key for clients and clientid is present in both messages and orders as foreign key. I want to get clientid, messagecount, and ordercount, that is, for each client I want to get number of messages posted by the client and number of orders placed by the client.

    To get clientid, messagecount I do this:

    select a.clientid, count(b.clientid) as messagecount
    from clients a left outer join messages b on a.clientid = b.clientid
    group by a.clientid

    To get clientid, ordercount I do this:

    select a.clientid, count(b.clientid) as ordercount
    from clients a left outer join orders b on a.clientid = b.clientid
    group by a.clientid

    But I am not able to get all three clientid, messagecount, ordercount in a single query. This is what I tried. But it is not giving me the correct answer.

    select a.clientid, count(b.clientid) as messagecount, count(c.clientid) as ordercount
    from clients a
    left outer join messages b on a.clientid = b.clientid
    left outer join orders c on a.clientid = c.clientid
    group by a.clientid

    Can any one tell me how to do it right?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    SELECT c.clientid
    ,  (SELECT Count(*)
          FROM messages AS m
          WHERE m.clientID = c.clientID) AS message_count
    ,  (SELECT Count(*)
          FROM orders AS o
          WHERE o.clientID = c.clientID) AS order_count
       FROM clients AS c
    -PatP

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    Works great. Thanks Pat.

Posting Permissions

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