A query within one query

[AndyPSV]

I've got a table.

 

id group cid u date id_

7 1 answer_comment 6 2012-01-14 18:13:27 9#8

2 1 question_comment 5 2012-01-14 16:01:40 9

3 1 question_follow 5 2012-01-14 16:01:40 9

4 0 answer_anyoneVote 5 2012-01-14 16:01:40 11

5 0 question_follow 5 2012-01-14 16:01:40 8

6 1 question_follow 5 2012-01-14 16:01:40 21

 

& I've got a mysql_query, e.g.:

 

SELECT * FROM xernt_mailing WHERE date <= "2012-01-14 19:32:17" AND `group` = "1" AND u = "6" ORDER BY cid DESC

 

____ What I want to do, is to select ORDER of "cid" FROM ANOTHER TABLE, preferable: ___

 

 

CREATE TABLE `xernt_cid_seq` (

  `cid` varchar(255) NOT NULL,

  `seq` int(11) NOT NULL,

  KEY `cid` (`cid`,`seq`)

) ENGINE=MyISAM DEFAULT CHARSET=latin2;

 

--

-- Dumping data for table `xernt_cid_seq`

--

 

INSERT INTO `xernt_cid_seq` (`cid`, `seq`) VALUES

('question_answer', 10),

('question_editDescr', 200),

('question_editT', 100);

 

 

------------- (preview)

cid seq

question_answer 10

question_editDescr 200

question_editT 100

 

 

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

 

I was combining with:

 

SELECT * FROM xernt_mailing WHERE date <= "2012-01-14 19:32:17" AND `group` = "1" AND u = "6" ORDER BY (SELECT cid FROM xernt_cid_seq WHERE cid = '...... ? ORDER BY seq DESC) DESC

 

but need to figure how do it: could anyone help?

 

 

 

----

didn't added first table:

 

 

 

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=8 ;

 

--

-- Dumping data for table `xernt_mailing`

--

 

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

(7, 1, 'answer_comment', 6, '2012-01-14 18:13:27', '9#8'),

(2, 1, 'question_comment', 5, '2012-01-14 16:01:40', '9'),

(3, 1, 'question_follow', 5, '2012-01-14 16:01:40', '9'),

(4, 0, 'answer_anyoneVote', 5, '2012-01-14 16:01:40', '11'),

(5, 0, 'question_follow', 5, '2012-01-14 16:01:40', '8'),

(6, 1, 'question_follow', 5, '2012-01-14 16:01:40', '21');

 

 

maybe making it more clear: so the "cid" would be selected by the order of sequence that is attached to their name (as selected from another table)

[trq]

Don't you mean you want to ORDER BY seq from the other table? Either way you need a JOIN.

 

SELECT id, `group`, cid, u, `date`, id_
FROM xernt_mailing
LEFT JOIN othertable USING(cid)
WHERE date <= "2012-01-14 19:32:17" AND `group` = 1 AND u = 6
ORDER BY seq DESC

[AndyPSV]

thank you.