SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining tables and counting

    Im trying to join two tables (cats & links)and I want to count and group by the cat_id in the links table

    I think I have the join right
    Code:
    "SELECT * from cats left join links on cats.cat_id = links.cat_id"
    But I just can't work out the count and group by part.

    Can anyone help?

    Cheers

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So you want to know the total number of records?

    Code:
    SELECT COUNT(*) as totalcount from cats
    INNER JOIN links on cats.cat_id = links.cat_id
    It would be better to replace the * with one of your field names. This will return the total number of records the query returns.

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Monoloid, with you help I have now come up with
    Code:
    SELECT COUNT(links.cat_id) as num FROM links INNER JOIN cats on cats.cat_id = links.cat_id  group by links.cat_id
    This is close to what I want to do, I also want to retrive all columns fom both tables.
    How would I do that?

    Cheers

  4. #4
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT COUNT(links.cat_id) as num, field1, field2, field3, field4 FROM links 
    INNER JOIN cats on cats.cat_id = links.cat_id 
    GROUP BY links.cat_id, field1, field2, field3, field4
    It's always better to actually write out your fields, rather than SELECT *

  5. #5
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, now I've got
    Code:
    "SELECT COUNT(links.cat_id) as num FROM links
     INNER JOIN cats on cats.cat_id = links.cat_id  group by links.cat_id, cat_link, cat_text"
    This seems to work i.e it doesn't give any errors but when I try it in the following code it does not echo anything for the cat_link & cat_text fields
    PHP Code:
    $q = ("SELECT COUNT(links.cat_id) as num FROM links INNER JOIN cats on cats.cat_id = links.cat_id  group by links.cat_id, cat_link, cat_text");
    $result mysql_query($q) or die (mysql_error());

    while( 
    $row mysql_fetch_array($result,MYSQL_ASSOC))

        {
            echo 
    "$row[cat_text] -- $row[cat_link]-- $row[num]<br />";
        } 
    Thanks

  6. #6
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by Mongoloid
    It would be better to replace the * with one of your field names.
    That's ok for a SELECT but for a COUNT it depends on whether your columns can or can't be NULL.

    Quote Originally Posted by Mongoloid
    This will return the total number of records the query returns.
    That's right for count(*) but probably not for count(links.cat_id). The second one actually returns the total number of rows where links.cat_id is NOT NULL.

    Quote Originally Posted by doolallay
    This is close to what I want to do, I also want to retrive all columns fom both tables. How would I do that?
    This can't be done. If you enclose all columns from both tables you will get the count for every DISTINCT combination of those columns, which most probably will result in count being always 1.

    Quote Originally Posted by doolallay
    This seems to work i.e it doesn't give any errors but when I try it in the following code it does not echo anything for the cat_link & cat_text fields
    As you do not have neither cat_link nor cat_text in your SELECT, $row does not have indexes with those keys, but just $row['num']. Also it is definitely bad advice to not include links.cat_id, cat_link, cat_text in your SELECT. Although it's possible with MySQL it can give you unpredictable results - even the manual says so!

    Although I do not exactly understand what you actually want to achieve you may try this one:

    Code:
    SELECT COUNT(*) as num, cat_link, cat_text
    FROM links
    INNER JOIN cats
    ON cats.cat_id = links.cat_id
    GROUP BY cat_link, cat_text;
    Edit:


    edited twenty minutes after initial post:
    GROUP BY added
    sorry for that
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  7. #7
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kleineme
    Although I do not exactly understand what you actually want to achieve you may try this one:
    Thanks Kleineme,

    Perhapes it would be a good idea if I displayed the tables and re-explain sorry if I wasent clear before.
    Code:
    #
    # Table structure for table `cats`
    #
    
    CREATE TABLE cats (
      cat_id int(25) NOT NULL auto_increment,
      cat_link varchar(25) NOT NULL default '',
      cat_text varchar(25) NOT NULL default '',
      PRIMARY KEY  (cat_id)
    ) TYPE=MyISAM COMMENT='cats';
    # --------------------------------------------------------
    
    #
    # Table structure for table `links`
    #
    
    CREATE TABLE links (
      link_id int(25) NOT NULL auto_increment,
      cat_id int(7) NOT NULL default '0',
      link_name varchar(25) NOT NULL default '',
      link_title tinytext NOT NULL,
      link_posted datetime NOT NULL default '0000-00-00 00:00:00',
      link_post text NOT NULL,
      link_viewed int(7) NOT NULL default '0',
      link_followed int(7) NOT NULL default '0',
      link_url tinytext NOT NULL,
      PRIMARY KEY  (link_id)
    ) TYPE=MyISAM COMMENT='links';
    I want to count and group by cat_id the amonunt of rows in the the links table and retrive the cat_text and cat_link from the cats table.

    I hope that's clearer

    Thanks

  8. #8
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yippie!

    Got it now
    Code:
    SELECT cats.cat_id, cat_text, COUNT
    (links.cat_id) AS num from cats LEFT JOIN links ON 
    cats.cat_id = links.cat_id group by cat_id
    Thanks for your help with this people


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
  •