What's wrong with this inner query (MySQL)...

Question

...besides the fact that I am a total amateur?

My table is set up like this:

CREATE TABLE `messages` (
 `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
 `patient_id` int(6) unsigned NOT NULL,
 `message` varchar(255) NOT NULL,
 `savedate` int(10) unsigned NOT NULL,
 `senddate` int(10) unsigned NOT NULL,
 `SmsSid` varchar(40) NOT NULL COMMENT 'where we store the cookies
from twilio',
 `sendorder` tinyint(3) unsigned NOT NULL COMMENT 'the order we want
the msg sent in',
 `sent` tinyint(1) NOT NULL COMMENT '0=queued, 1=sent,
2=sent-unqueued,4=rec-unread,5=recd-read',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=143 ;

I need a query that will

SELECT * FROM `messages` WHERE `senddate` < $now AND `sent` = 0 (AND LIMIT
TO ONLY ONE RECORD PER `patient_id`)

I've tried the following:

SELECT * 
FROM `messages`
WHERE `senddate` IN 
    (SELECT `patient_id`, max(`senddate`)
     GROUP by `patient_id`) 
AND `senddate` < $now AND `sent` = 0 ;

But I get this error: MySQL client version: 5.1.37

`#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP by patient_id) AND senddate < 1270093898 AND sent = 0 LIMIT 0, 30' at line 5

Answer 1

You're missing a FROM clause in your inner SELECT statement. It should be:

SELECT `patient_id`, max(`senddate`) FROM messages
     GROUP by `patient_id`

Answer 2

Apart from missing a FROM clause in your sub query, the IN clause will be expecting a single column from the sub query. You are selecting two columns.

Change the sub query to have a FROM clause and return only the expected column:

SELECT * 
FROM `messages`
WHERE `senddate` IN 
    (SELECT max(`senddate`)
     FROM `patients`
     GROUP by `patient_id`) 
AND `senddate` < $now AND `sent` = 0 ;

Answer 3

This query should return one record per patient_id while preserving the conditions that your initial query contains:

SELECT `patient_id`, max(`senddate`)
FROM `messages`
WHERE `senddate` < $now AND `sent` = 0
GROUP BY `patient_id`

Answer 4

My mySQL syntax knowledge isn't spot on (I'm more SQL Server), but hopefully if I'm slightly wrong, you'll see what I'm trying to do!

SELECT m.*
FROM `messages` m
    JOIN 
    (
        SELECT patient_id, MAX(senddate) AS latestsenddate
        FROM `messages'
        WHERE `senddate` < $now AND `sent` = 0
    ) m2 ON m.patient_id = m2.patient_id AND m.senddate = m2.latestsenddate

This kind of approach means you can return ALL columns from messages, and just assumes that a patient won't have multiple messages with the same latest send date.

Answer 5

Final version:

SELECT * , MIN(  `senddate` ) 
FROM  `messages` 
WHERE  `senddate` < $now
AND  `sent` =0
GROUP BY  `patient_id`

Answer 6

how about this?

SELECT * 
FROM `messages`
WHERE `id` IN 
    (SELECT p1.id
     FROM `messages` p1 
       JOIN (SELECT `patient_id`, max(`senddate`) maxdate
             FROM `messages`
             GROUP by `patient_id`)  p2
       ON (p1.patient_id = p2.patient_id
           AND p1.senddate = p2.maxdate))
AND `senddate` < $now AND `sent` = 0 ;

Answer 7

You almost had it.

You were just missing the FROM clause in the sub query and the patient_id column in the WHERE clause.

SELECT * 
FROM `messages`
WHERE (`patient_id`, `senddate`) IN 
    (SELECT `patient_id`, max(`senddate`) FROM `messages`
     GROUP by `patient_id`) 
AND `senddate` < $now AND `sent` = 0;

This query assumes that there isn't more than one message with the same max senddate for the same patient, otherwise, you'll get multiple records for a patient.