Re: selecting more sum()
Hi, just Start here http://dev.mysql.com/doc/mysql/en/select.html mathias Selon Octavian Rasnita [EMAIL PROTECTED]: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting more sum()
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting more sum()
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
