skomplikowane zapytanie Opcje

[Anna]

Zapytanie powinno zwracać:
- dane wszystkich userów users_data.*
- sumę punktów zdobytych przez usera w danym teście SUM(users_asks.point)+SUM(answers.point), lub NULL jeśli user testu nie wypełniał
- maksymalną sumę punktów jaką user mógł zdobyć w danym teście

Macie jakieś pomysły? (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

Kombinuję w ten sposób:

[SQL] pobierz, plaintext 
SELECT users_data. * , ask.suma + ans.suma, ask.max + ans.max
FROM users_data LEFT JOIN (
SELECT SUM( users_asks.point ) AS suma, COUNT( asks.ask_id ) AS max, users_asks.user_id
FROM users_asks, asks
WHERE users_asks.ask_id = asks.ask_id
GROUP BY users_asks.user_id, asks.test_id
) AS ask
USING ( user_id )
LEFT JOIN (
 
SELECT SUM( answers.point ) AS suma, COUNT( asks.ask_id ) AS max, users_answers.user_id
FROM users_answers, answers, asks
WHERE users_answers.answer_id = answers.answer_id AND answers.ask_id = asks.ask_id
GROUP BY users_answers.user_id, asks.test_id
) AS ans
USING ( user_id )
[SQL] pobierz, plaintext 

(IMG:http://forum.php.pl/style_emoticons/default/questionmark.gif) (IMG:http://forum.php.pl/style_emoticons/default/questionmark.gif)

Ten post edytował Anna 16.03.2007, 17:38:56

[Anna]

Ależ proszę...

[SQL] pobierz, plaintext 
-- 
-- Struktura tabeli dla  `answers`
-- 
CREATE TABLE `answers` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `ask_id` int(11) NOT NULL,
  `answer` text,
  `point` tinyint(4) DEFAULT NULL,
  `enable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`answer_id`),
  KEY `answers_FKIndex1` (`ask_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `asks`
-- 
CREATE TABLE `asks` (
  `ask_id` int(11) NOT NULL AUTO_INCREMENT,
  `test_id` int(11) NOT NULL,
  `ask` text,
  `enable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`ask_id`),
  KEY `asks_FKIndex1` (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `tests`
-- 
CREATE TABLE `tests` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `timeout` int(11) DEFAULT NULL,
  `enable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users`
-- 
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(45) DEFAULT NULL,
  `passwd` varchar(45) DEFAULT NULL,
  `auth` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users_answers`
-- 
CREATE TABLE `users_answers` (
  `answer_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`answer_id`,`user_id`),
  KEY `users_answers_FKIndex1` (`user_id`),
  KEY `users_answers_FKIndex2` (`answer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users_asks`
-- 
CREATE TABLE `users_asks` (
  `user_id` int(11) NOT NULL,
  `ask_id` int(11) NOT NULL,
  `answer` text,
  `point` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`user_id`,`ask_id`),
  KEY `users_asks_FKIndex1` (`user_id`),
  KEY `users_asks_FKIndex2` (`ask_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users_data`
-- 
CREATE TABLE `users_data` (
  `user_id` int(11) NOT NULL,
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  PRIMARY KEY  (`user_id`),
  KEY `users_data_FKIndex1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

Myślałam, że uda się coś "na sucho" wykombinować (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)