How to select ROW within one query

[AndyPSV]

I've got:

 

SELECT DISTINCT u,`group` FROM xernt_mailing WHERE date <= DATE_ADD("2012-01-27 00:29:31",INTERVAL ________ HOUR) AND `group` <> "0"

 

And what I want to do is to select value of: `interval` field, from the table: u_notifications WHERE the ID, is the exact value of the "u" field

 

id group cid u date id_

25 1 question_comment 5 2012-01-27 00:29:09 7

26 1 question_comment 5 2012-01-27 00:29:09 1

27 1 question_follow 5 2012-01-27 00:29:09 1

28 1 question_follow 5 2012-01-27 00:29:09 6

29 1 question_follow 5 2012-01-27 00:29:09 7

 

-----------------

 

id interval

5 8

 

 

How to do it? (all must be done in 1 query)

 

 

 

@@@@@@@@@

 

 

CREATE TABLE `xernt_u_notifications` (

`id` int(11) NOT NULL auto_increment,

`interval` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=9 ;

 

--

-- Dumping data for table `xernt_u_notifications`

--

 

INSERT INTO `xernt_u_notifications` (`id`, `interval`) VALUES

(5, ;

 

 

 

CREATE TABLE `xernt_mailing` (

`id` bigint(20) NOT NULL auto_increment,

`group` tinyint(4) NOT NULL,

`cid` varchar(255) NOT NULL,

`u` int(11) NOT NULL,

`date` datetime NOT NULL,

`id_` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=30 ;

 

--

-- Dumping data for table `xernt_mailing`

--

 

INSERT INTO `xernt_mailing` (`id`, `group`, `cid`, `u`, `date`, `id_`) VALUES

(25, 1, 'question_comment', 5, '2012-01-27 00:29:09', '7'),

(26, 1, 'question_comment', 5, '2012-01-27 00:29:09', '1'),

(27, 1, 'question_follow', 5, '2012-01-27 00:29:09', '1'),

(28, 1, 'question_follow', 5, '2012-01-27 00:29:09', '6'),

(29, 1, 'question_follow', 5, '2012-01-27 00:29:09', '7');

[joel24]

something like

SELECT DISTINCT x.u,`group` FROM xernt_mailing x WHERE date <= DATE_ADD("2012-01-27 00:29:31",INTERVAL (SELECT `interval` FROM u_notifications u WHERE u.id = x.u LIMIT 1) HOUR) AND `group` <> "0"

[AndyPSV]

Sorry, did it on the wrong query. The valid query to put it on is:

 

SELECT id,`group`,cid,u,`date`,id_ FROM xernt_mailing LEFT JOIN xernt_cid_seq USING(cid) WHERE date <= "2012-01-27 09:53:44" AND `group` = "1" AND u = "6" ORDER BY seq ASC

 

Did I do it correctly? (just to make sure, but still testing)

 

SELECT id,`group`,cid,u,`date`,id_,x.u FROM xernt_mailing x LEFT JOIN xernt_cid_seq USING(cid) WHERE date <= DATE_ADD("2012-01-27 09:55:47",INTERVAL (SELECT `interval` FROM xernt_u_notifications u WHERE u.id = x.u LIMIT 1) HOUR) AND `group` = "1" AND u = "6" ORDER BY seq ASC

SEEM TO WORK, so thank you.