1

I have a database with the following structure

  • Items table: itemID, itemName
  • Ads table: AdsId, itemID

I would like to make pagination on items table, 10 items in each page, but first I must retrieve items whose IDs are in the Ads table, and then retrieve other items.

I know I must use limit such as:

SELECT * FROM Items LIMIT $offset, $no_of_records_per_page

And may I joined it with adsTable, such as:

SELECT * FROM Items inner join Ads on Ads.itemID = Items.itemID
LIMIT $offset, $no_of_records_per_page

but how can I achieve what I described? Thanks in advance.

2 Answers 2

0

METHOD #1

You could create a temp table called itemID_paginator and paginate this

CREATE TABLE items_paginator AS SELECT itemID from Items WHERE 1=2;
ALTER TABLE items_paginator ADD PRIMARY KEY (itemID);
INSERT INTO items_paginator SELECT itemID from Items;

You then paginate against itemID_paginator and join it to Ads

SELECT A.*,B.* FROM 
(
    SELECT itemID FROM itemID_paginator ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) PG
INNER JOIN Items A ON PG.ItemID = A.ItemID
INNER JOIN Ads   B ON A.ItemID  = B.ItemID;

METHOD #2

If you prefer not to use a temp table, then perform LIMIT directly to the Items table

SELECT A.*,B.* FROM 
(
    SELECT * FROM Items ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) A INNER JOIN Ads B ON A.ItemID  = B.ItemID;

I suggested these methods before in the following posts:

GIVE IT A TRY !!!

3
  • Thanks for your reply, but what I would like to do is to get items whose ids in ads table, when retrieving those items finished then get other items not in ads table. Feb 5, 2021 at 6:14
  • If you want to see ItemIDs that do not have ads, change INNER JOIN to LEFT JOIN. Feb 5, 2021 at 12:44
  • If you want all ItemIDs that have no ads after, add ORDER BY B.ISNULL(B.ItemID) after the LEFT JOIN. Feb 5, 2021 at 12:45
0

You really should use ORDER BY when using LIMIT. Else the rows you get will be unpredictable.

Yes, JOIN works. Think of it this way: A JOIN combines tables in a specified way and (logically) creates a new table that contains the combined stuff. After the JOIN is done, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are 'performed', in that order.

I say, "logically" because the Optimizer has a lot of tricks to make the query more efficient than blindly creating a temp table, then filtering, then (etc).

SQL specifies what you want in a rigorous way. The Optimizer is free to rearrange things as long as it gets the same answer.

OFFSET is the obvious way to do pagination. But it leads to bugs (not in MySQL, but in its application on web sites.) And performance suffers because the rows skipped are actually stepped over one-by-one. More: http://mysql.rjweb.org/doc.php/pagination

1
  • Thanks for your explanation,here (when I asked) forget to write ORDER BY with my query, but that is didn't answer what a want to achieve. Feb 5, 2021 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.