MySQL Forums
Forum List  »  Views

Query not returning what I expect
Posted by: Bodi K
Date: April 26, 2013 11:42AM

I'm not sure why I'm having a brain freeze today, but I've gone in circles long enough, so I'm here for some sanity.

For background purposes. There are 17 questions in this table:
CREATE TABLE `question` (
`ID` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
`Question` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8$$


The real work is done here:
CREATE TABLE `client-question` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ClientID` int(11) NOT NULL,
`QuestionID` tinyint(4) unsigned NOT NULL,
`UserID` int(11) DEFAULT NULL,
`Value` tinyint(3) unsigned DEFAULT NULL,
`Timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
KEY `ClientID` (`ClientID`),
KEY `QuestionID` (`QuestionID`),
KEY `UserID` (`UserID`)
) ENGINE=MyISAM AUTO_INCREMENT=18115 DEFAULT CHARSET=utf8$$

I have another (insignificant for this post) table that has 1000 Clients, and another table that has 5 users. Each of the 5 user needs to answer the 17 questions for each of the 1000 clients. By default I have populated the client-question table with the necessary records (5x17x1000 rows). Value is null and Timestamp is null so that each user can see on their screen that they need to answer that question.


Here comes the tricky part. If it's a new record then the Value gets filled in with whatever they choose from the dropdown (1-5 from a combobox) . If there is already a Value then it creates a new record (so I can keep track of history as the user changes their mind about this client). The form needs to display always the most current Value entered for that client and user. It also needs to display all 17 questions.

Scenario:
UserID 1 is on the screen of ClientID 1. They have already answered QuestionID 1 five times, but the other 16 question have never been answered, so the 16 pre-populated Value and Timestamp records are null.

I need a query to return the 16 null questions and the most recent Value of the one answered question. I'm thinking something like this, but it doesn't work!

SELECT
MAX(ID),
ClientID,
QuestionID,
UserID,
`Value`,
`Timestamp`
FROM
`client-question`
WHERE UserID=1 OR UserID IS NULL
GROUP BY ClientID, QuestionID, UserID
HAVING ClientID = 1

It successfully returns the 16 questions that haven't been answered by anybody yet; it returns the 1 question that HAS been answered by UserID 1, and it even returns the correct row ID; however, it returns a Value and Timestamp from the first row, not the Value and Timestamp from the row that it should be (in this case the ID of the MAX(ID) is 18114, but the Value and Timestamp for the returned data is from ID 884). Ideally, in Access, I would use LAST(Value), LAST(Timestamp) but it doesn't return as expected.

Any ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
Query not returning what I expect
4704
April 26, 2013 11:42AM


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.