List:General Discussion« Previous MessageNext Message »
From:Octavian Rasnita Date:June 23 2005 4:59am
Subject:selecting more sum()
View as plain text  
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



Thread
selecting more sum()Octavian Rasnita23 Jun
  • Re: selecting more sum()mfatene23 Jun
  • Re: selecting more sum()Peter Brawley23 Jun