Sample with merge and limit

Question

Good afternoon, All good.

In general, there are 3 tables. You need to select all values, but limit the selection to the limit. In order for the values to be selected by 9 pieces, across the TEXT.id field, and here I do not understand how to do this .

I explain by the fact that it is not quite correctly stated the question.

You need to select 9 records, regardless of how many values are in the SRC table.

For example, in the SRC table 200 records and in the TEXTS table 20 records.

You need to select all the entries where SRC.event_id are the same, but that the sample began from 9 to 18 of the event_i.

CREATE TABLE `SRC`( `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE , `event_id` int(10) NOT NULL , `big_src` VARCHAR(100) NOT NULL , `small_src` VARCHAR(100) NOT NULL , `alternate` VARCHAR(50) NOT NULL, FOREIGN KEY (event_id) REFERENCES TEXTS(id) )ENGINE = MyISAM DEFAULT CHARSET =utf8 COLLATE = utf8_unicode_ci;

 CREATE TABLE `TEXTS`( `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE , `big_text` VARCHAR(100) NOT NULL , `small_text` VARCHAR(100) NOT NULL , `datetime` DATETIME NOT NULL )ENGINE = MyISAM DEFAULT CHARSET =utf8 COLLATE = utf8_unicode_ci;

 CREATE TABLE `ALL`( `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE , `TEXT_id` int(10) NOT NULL , `SRC_event_id` int(10) NOT NULL, FOREIGN KEY (TEXT_id) REFERENCES TEXTS(id), FOREIGN KEY (SRC_event_id) REFERENCES SRC(event_id) )ENGINE = MyISAM DEFAULT CHARSET =utf8 COLLATE = utf8_unicode_ci;

 SELECT event_id,small_src,alternate,small_text,datetime FROM `TEXTS` INNER JOIN `SRC` ON `TEXTS`.id=`SRC`.event_id INNER JOIN `ALL` ON `TEXTS`.id=`ALL`.TEXT_id ;

I explain by the fact that it is not quite correctly stated the question.
You need to select 9 records, regardless of how many values are in the SRC table.
For example, in the SRC table 200 records and in the TEXTS table 20 records.
It is necessary to select all the records where SRC.event_id are the same, but that the sample began with, for example, 9 to 18 event_id.

Accepted Answer · 2017-11-29T17:26:42

Suppose you are looking for something like:

 SELECT t.id event_id,s.small_src,s.alternate,a.small_text,t.datetime FROM `TEXTS` t JOIN `SRC` s ON s.event_id = t.id JOIN `ALL` a ON a.TEXT_id = t.id WHERE t.id IN (SELECT id FROM TEXTS ORDER BY id LIMIT 9 OFFSET 1*9)

The query selects by pages in texts.id and the data associated with them. The page is selected by the parameter offset (0 * 9, 1 * 9, 2 * 9, etc.)
Probably it is worthwhile to use the left join if the data associated with some texts.id may not be.

[42000] [1235] This version of MySQL doesn’t support 'LIMIT & IN / ALL / ANY / SOME subquery'

 SELECT t.id event_id,s.small_src,s.alternate,a.small_text,t.datetime FROM `TEXTS` t JOIN `SRC` s ON s.event_id = t.id JOIN `ALL` a ON a.TEXT_id = t.id INNER JOIN (SELECT id FROM TEXTS ORDER BY id LIMIT 9 OFFSET 1*9) t2 ON t.id = t2.id

Yes, it is something similar to the truth but writes [42000] [1235] This version of MySQL doesn’t support 'LIMIT & IN / ALL / ANY / SOME subquery'
transfer 0, 9, 18, 27 ... You can also use the syntax LIMIT 27, 9

Anton Skorodumov Anton Skorodumov 767 2 9 · Answer 2 · 2017-11-29T16:32:59

 SELECT event_id,small_src,alternate,small_text,datetime FROM `TEXTS` INNER JOIN `SRC` ON `TEXTS`.id=`SRC`.event_id INNER JOIN `ALL` ON `TEXTS`.id=`ALL`.TEXT_id order by event_id limit 9 OFFSET 9

limit 9 - how many records to display

OFFSET 9 - how much to skip

If I correctly understood the essence of the question.

Not really, the fact is that I need the number of event_id can be both 3 and 23 and 100. And you need to select only 9 event_id
SELECT SRC.id, event_id, small_src, alternate, small_text, datetime FROM TEXTS INNER JOIN SRC ON TEXTS.id = SRC.event_id WHERE event_id = 1;
That each time not to substitute instead of 1 other numbers.
If you need a data range, use WHERE event_id between value_from and value_to

Sample with merge and limit

2 answers 2

More articles: