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
