MySQL: Data pagination according data on another table

join;MySQLpagination

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.

Best Answer

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 !!!

Related Question