MySQL Forums
Forum List  »  MyISAM

two counts and joins
Posted by: htr htr
Date: March 07, 2006 01:52AM

Hi i have tables:
CREATE TABLE bonus (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
points TINYINT UNSIGNED NULL,
id_user INTEGER UNSIGNED NULL,
PRIMARY KEY(id)
);

CREATE TABLE articles (
id_article INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
points TINYINT UNSIGNED NULL,
publish TINYINT UNSIGNED NULL DEFAULT 0,
id_user INTEGER UNSIGNED NULL,
PRIMARY KEY(id_article)
);

CREATE TABLE users (
id_user INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
login VARCHAR(20) NULL,
PRIMARY KEY(id_user)
);

now i selecting all users, and sum their points, and bouns points:
SELECT login, u.id_user, SUM(a.points) AS punkty, SUM(b.points) AS bonus FROM users u
LEFT OUTER JOIN bonus b ON u.id_user=b.id_user
LEFT OUTER JOIN articles a ON u.id_user=a.id_user
GROUP BY id_user
ORDER by u.id_user ASC

but now i need to count normal points only where articles.publish=1

any one know how to do it right?
(show all users with sum(normal points[where a.publish=1]) and sum(bouns points)) in one query ofcourse :)



Edited 1 time(s). Last edit at 03/07/2006 02:03AM by htr htr.

Options: ReplyQuote


Subject
Views
Written By
Posted
two counts and joins
2364
March 07, 2006 01:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.