Glue 2 queries

Question

I can not glue these 2 requests

SELECT q.* FROM `subscribers` AS sub, `questions` AS q WHERE (sub.id = 1 AND q.to = sub.id_subscriber) ORDER BY `time` DESC LIMIT 20 SELECT d.* FROM `subscribers` AS sub, `discussion` AS d WHERE (sub.id = 1 AND d.to = sub.id_subscriber) ORDER BY `time` DESC LIMIT 20

Do so

 ( SELECT q.`id`, q.`text`, q.`reply`, q.`time`, q.`from`, q.`to`, q.`ip`, q.`is_anonum`, 'null' AS q.`question_id` FROM `subscribers` AS sub, `questions` AS q WHERE (sub.id = 1 AND q.to = sub.id_subscriber) ORDER BY `time` DESC LIMIT 20 )UNION ( SELECT d.`id`, d.`text`, d.`reply`, d.`time`, d.`from`, d.`to`, d.`ip`, d.`is_anonum`, 'null' AS d.`likes`, 'null' AS d.`discussion`, 'null' AS d.`salt` FROM `subscribers` AS sub, `discussion` AS d WHERE (sub.id = 1 AND d.to = sub.id_subscriber) ORDER BY `time` DESC LIMIT 20 ) ORDER BY `time` DESC

But they send me where to further, here’s a dump if that

 CREATE TABLE `discussion` ( `id` int(11) NOT NULL auto_increment, `question_id` int(11) NOT NULL, `text` TEXT NOT NULL, -- Вопрос `reply` TEXT NOT NULL, `time` int(11) NOT NULL, `from` int(11) NOT NULL, `to` int(11) NOT NULL, `ip` varchar(25) NOT NULL default '', `is_anonum` TINYINT(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=cp1251; CREATE TABLE `questions` ( `id` int(11) NOT NULL auto_increment, `text` TEXT NOT NULL, `reply` TEXT NOT NULL, `time` int(11) NOT NULL, `likes` int(8) NOT NULL default '0', `discussion` int(8) NOT NULL default '0', `salt` char(3) NOT NULL default '', `from` int(11) NOT NULL, `to` int(11) NOT NULL, `ip` varchar(25) NOT NULL default '', `is_anonum` TINYINT(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=cp1251; CREATE TABLE `subscribers` ( `id` int(11) NOT NULL, -- id юзера `id_subscriber` int(11) NOT NULL, -- На кого подписан юзер `time` int(11) NOT NULL ) ENGINE = MyISAM DEFAULT CHARSET=cp1251;

If you need more others, then in one query you need to output a constant to the result (such 0 as likes )

Accepted Answer · 2013-07-22T08:25:57

But they send me where to

You probably get an error message. Your statement is absolutely not informative.

I'll try to help.

Record of this kind:

'null' AS q. question_id

is incorrect and leads to an error. Most likely, you confused the values of places
and actually wanted to write like this (forgetting to connect the table in the sample condition):

 q.`question_id` AS 'null'

But even if you correct all the errors of this kind, you have one more:

UNION can only be used when
all queries return the same number of columns,
which contain compatible data types .

In your first query, it returns two columns less.

Correct these errors and the request will work.

It seems I understood my mistake, corrected it, now writes "Unknown column 'q.question_id' in 'field list'" (SELECT q. id , q. text , q. reply , q. time , q. from , q. to , q . ip , q. is_anonum , q. question_id AS 'null', q. likes , q. discussion , q. salt ...) UNION (SELECT d. id , d. text , d. reply , d. time , d . from , d. to , d. ip , d. is_anonum , d. question_id , d. likes AS 'null', d. discussion AS 'null', d. salt AS 'null' ...) ORDER BY time DESC
question_id is in your discussion table, not in question. Discussion table is not connected to you either in the FROM block or in the WHERE clause. Don't forget to add a condition: q.
Everything turned out, thanks) But I’m worried about one question, are there two similar requests to the subscribers table, can they somehow be reduced to one?

fori1ton fori1ton 22.2k 3 38 63 · Answer 2 · 2013-07-22T08:00:31

In order to “glue” two queries with the UNION operator, the number of columns in both queries must be the same. Also the column types must match.

if you strongly need to re-sort - you need to wrap in another select

Glue 2 queries

2 answers 2

More articles: