From: Peter Brawley
Date: June 23 2005 2:03pm
Subject: Re: selecting more sum()
List-Archive: http://lists.mysql.com/mysql/185793
Message-Id: <42BAC125.2020501@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

Octavian,

You seem to be missing an articles column linking it to newspapers. 
Supposing an id_newspapers column in articles, your query would be 
something like

SELECT
  articles.title,
  CHAR_LENGTH(body) AS Length,
  newspapers.name AS Newspaper,
  COUNT(visitors.id) AS Visits,
  COUNT(comments.id) AS Comments
FROM articles
  INNER JOIN newspapers ON articles.id_newspapers = newspapers.id
  LEFT JOIN visitors ON articles.id = visitors.id_articles
  LEFT JOIN comments ON articles.id = comments.id_articles
GROUP BY articles.id

For how to build such queries look at the manual pages for aggregate 
functions and for GROUP BY.

PB

-----
Octavian Rasnita wrote:

>Hi,
>
>I have the following tables:
>
>create table articles(
>id int unsigned not null primary key,
>title varchar(255) not null,
>body text not null
>);
>
>create table newspapers(
>id int unsigned not null primary key,
>name varchar(255) not null
>);
>
>create table visitors(
>id int unsigned not null primary key,
>id_articles int unsigned not null
>);
>
>create table comments(
>id int unsigned not null primary key,
>id_articles int unsigned not null
>);
>
>I would like to select:
>- the title from `articles`
>- the length of the body from `articles`
>- the name of the newspaper which correspond to the title of the article
>- the number of visitors each articles have (count(*) from visitors where
>articles.id=visitors.id_articles)
>- the number of comments each articles have (count(*) from comments where
>articles.id=comments.id_articles)
>
>I don't know how to select the last 2 elements (the number of visitors and
>the number of comments).
>
>I want to select all the articles from `articles` even if there are no
>visitors or no comments in the `visitors` and `comments` tables, so I might
>need using "left join".
>
>I have tried a few ways of doing this, but without any result.
>
>Please help me if you can.
>
>(I have written the table definitions above right in the email client, so
>they are not tested, but I think they are correct)
>
>Thank you.
>
>Teddy
>
>
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.11/26 - Release Date: 6/22/2005