I'm having this very complicated problem getting the right results out of my tables.
These are the tables:
CREATE TABLE `Answers` (
`id` int(10) unsigned NOT NULL auto_increment,
`question_id` int(10) unsigned NOT NULL default '0',
`session_id` varchar(255) NOT NULL default '',
`answer` text NOT NULL,
)
CREATE TABLE `Sessions` (
`session_id` varchar(255) NOT NULL default '',
`user_id` int(10) unsigned NOT NULL default '0',
`start_time` timestamp(14) NOT NULL,
)
(Indexes and keys omitted)
For people who use this online survey system, a new session is created everytime they log in (a new record is then created in table `Sessions` for their user_id.
The questions they answer are stored in table `Answers`, using the new session_id.
Now this is important: The user doesn't have to re-submit the answer to every question he answered in the first session. Only the updated answers are stored in the table.
Now here's what i need to do:
I need, for every question, and every user, the answer that was given for the most recent session in which the particular user answered the question.
I got this far:
SELECT Answers.question_id, Answers.answer, Sessions.user_id, Sessions.session_id
FROM (Answers INNER JOIN Sessions USING(session_id)) LEFT JOIN Sessions AS S2 ON (Sessions.start_time<S2.start_tijd) AND (Sessie.deelnemer_id=S2.deelnemer_id) WHERE S2.start_tijd IS NULL
I tried to get the joined data from Answers/Sessions for which no record exists in S2. But instead i get only the Answers for `Sessions` records that don't exist in S2. So only answers from the last session per user.
Is there a solution possible for this problem using Mysql 3.23?
These are the tables:
CREATE TABLE `Answers` (
`id` int(10) unsigned NOT NULL auto_increment,
`question_id` int(10) unsigned NOT NULL default '0',
`session_id` varchar(255) NOT NULL default '',
`answer` text NOT NULL,
)
CREATE TABLE `Sessions` (
`session_id` varchar(255) NOT NULL default '',
`user_id` int(10) unsigned NOT NULL default '0',
`start_time` timestamp(14) NOT NULL,
)
(Indexes and keys omitted)
For people who use this online survey system, a new session is created everytime they log in (a new record is then created in table `Sessions` for their user_id.
The questions they answer are stored in table `Answers`, using the new session_id.
Now this is important: The user doesn't have to re-submit the answer to every question he answered in the first session. Only the updated answers are stored in the table.
Now here's what i need to do:
I need, for every question, and every user, the answer that was given for the most recent session in which the particular user answered the question.
I got this far:
SELECT Answers.question_id, Answers.answer, Sessions.user_id, Sessions.session_id
FROM (Answers INNER JOIN Sessions USING(session_id)) LEFT JOIN Sessions AS S2 ON (Sessions.start_time<S2.start_tijd) AND (Sessie.deelnemer_id=S2.deelnemer_id) WHERE S2.start_tijd IS NULL
I tried to get the joined data from Answers/Sessions for which no record exists in S2. But instead i get only the Answers for `Sessions` records that don't exist in S2. So only answers from the last session per user.
Is there a solution possible for this problem using Mysql 3.23?