Professional Web Applications Themes

display records with a 0 count value - MySQL

Suppose I have two tables foo and bar. bar has many foos. table: foo id bar_id table: bar id I want to get a list of how many foos belong to each bar. This can be accomplished with a simple count/group by query such as: select bar.id, count(1) from foo, bar where bar.id=foo.bar_id group by bar.id order by count(1); This works just fine, except for the case when a bar has no foos. Then, instead of displaying the bar id and '0' for the count, mysql simply doesn't display that particular bar at all. Is there a simple way to ...

  1. #1

    Default display records with a 0 count value

    Suppose I have two tables foo and bar. bar has many foos.
    table: foo
    id
    bar_id

    table: bar
    id

    I want to get a list of how many foos belong to each bar. This can be
    accomplished with a simple count/group by query such as:
    select bar.id, count(1) from foo, bar where bar.id=foo.bar_id group by
    bar.id order by count(1);
    This works just fine, except for the case when a bar has no foos.
    Then, instead of displaying the bar id and '0' for the count, mysql
    simply doesn't display that particular bar at all. Is there a simple
    way to display bars that have no associated foos?

    Thanks,
    Joey Hurst

    jhurst Guest

  2. #2

    Default Re: display records with a 0 count value

    jhurst <com> schrieb: 

    You may want to look into "OUTER JOIN"s.

    HTH,
    --
    Philipp Tölke
    PGP: 0x96A1FE7A
    Philipp Guest

  3. #3

    Default Re: display records with a 0 count value

    Thank you!

    On Oct 16, 12:23 pm, Philipp Tölke <de> wrote: 
    >
    > HTH,
    > --
    > Philipp Tölke
    > PGP: 0x96A1FE7A[/ref]

    jhurst Guest

Similar Threads

  1. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  2. Replies: 3
    Last Post: October 14th, 03:44 PM
  3. Get count of child records
    By Ricardo in forum IBM DB2
    Replies: 2
    Last Post: September 25th, 06:09 AM
  4. newbie : trying to count records in mysql table
    By jim in forum PHP Development
    Replies: 1
    Last Post: July 11th, 02:25 PM
  5. When records don't display that are there
    By Gerry in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 09:11 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139