SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot LORDGreg's Avatar
    Join Date
    May 2001
    Location
    Ptuj, Slovenia
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT and count(*) in one QUERY

    I have 3 tables: news, comments and types.

    You can check their structure and some inserts in the file attached.

    All i want to know is this:

    Is it possible to gather all news info WITH the info on how many comments does every news have in one single query?

    i have two queries now, and if i count for every news is another query called:

    PHP Code:
    $q1 mysql_query ("SELECT id, username, subject, text, date FROM news ORDER BY date DESC LIMIT 0, 10");

    while (
    all news...)
    {

      
    $q2 mysql_query ("SELECT  count(c.id) AS num FROM comments AS c, types AS t WHERE t.type='news' AND t.id=c.typeid AND c.parentid="$q1->id ."");



    i hope you get the point...


    now i want to know if it is possible to get above two queries so that mysql will retuirn me something like this:

    id, username, subject, text, date => FROM NEWS AND
    num of comments => FROM COMMENTS

    ->

    id, username, subject, text, date, num_comments (for every news that is)


    thanks and best regards all
    Attached Files Attached Files
    lordgreg, sloreactor.com
    http://www.sloreactor.com/

  2. #2
    SitePoint Enthusiast Grubilo's Avatar
    Join Date
    Oct 2002
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    SELECT COUNT(c.id) as number_of_comments,
    n.id, news.username,
    n.subject,
    n.text,
    n.date FROM news as n, comments as c, types as t
    WHERE
    t.type='news' AND t.id=c.typeid AND c.parentid=n.id
    GROUP BY c.parentid

    Perhaps I made error in names...but the logic should be ok.
    Web tutorials
    http://webclass.ru

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    grubilo, your logic is okay provided you change your GROUP BY clause (i'm assuming you figured out how to join the tables, i didn't look at that)

    every non-aggregate field in the SELECT list has to be in the GROUP BY

    yes, mysql lets you get away with less, but be warned, that is non-standard behaviour and will backfire on you in other databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try unioning.


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
  •