MYSQL JOIN TWO TABLES (+ LIMIT based on first table)

Question

I have 2 tables like these:-

Table: offers  
|-------------- |
| id | OfferNum |
| 1  | a1       |
| 2  | a2       |
| 3  | a3       |
| 4  | a4       |
| 5  | a5       |
| 6  | a6       |
|---------------|

Table: colours 
|------------------------------------------|
| id | OfferNum  | colour    | availaility |
| 1  | a1        |blue       |yes          |
| 2  | a1        |red        |no           |
| 3  | a2        |green      |yes          |
| 4  | a3        |white      |yes          |
| 5  | a3        |brown      |yes          |
| 6  | a3        |navy       |no           |
| 7  | a3        |black      |yes          |
| 8  | a3        |red        |yes          |
| 9  | a4        |yellow     |no           |
| 10 | a5        |black      |yes          |
| 11 | a6        |white      |yes          |
|------------------------------------------|

For pagination purposes, I need to select 3 OfferNums from table "offers", starting from offset 0, and join the two tables so that the resultant rows would contain the 3 offernums (i.e a1, a2, and a3). And so on..

The following script, with LIMIT 0,3 does not produce the desired result.

SELECT offers.OfferNum, items.colour, items.availability
FROM offers
    JOIN items ON items.OfferNum = offers.OfferNum
ORDER BY offers.id ASC 
LIMIT 0 , 3

it yields the first 3 rows of the joined tables only. Like so:-

|----------------------------|
|OfferNum|colour|availability|
|a1      |blue  |yes         |
|a1      |red   |no          |
|a2      |green |yes         |
|----------------------------|

Is there a way to achieve the desired result?

@Quest SELECT * FROM `offers`,colours WHERE offers.OfferNum = colours.OfferNum and colours.OfferNum IN (select OfferNum from offers order by OfferNum asc limit 0,3) - this once and let me know whether it works or not — Ripa Saha, Nov 26 '19 at 13:41
Ripa Saha: It yields this error:- Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select OfferNum from offers order by OfferNum asc limit 0,3) LI — Quest, Nov 26 '19 at 15:41

score 2 · Accepted Answer · answered Nov 26 '19 at 13:27

2

If I understand correctly, you want to display 3 offers from offers table and display all equivalent values from the second table alongside, then you might consider selecting the data that you want to have as base, like:

SELECT OfferNum
    FROM offers
ORDER BY id ASC 
    LIMIT 0 , 3

Then select from it and join it with the type of JOIN you require The query would look like:

SELECT customOffers.OfferNum, items.colour, items.availability
FROM 
(SELECT OfferNum
    FROM offers
ORDER BY id ASC 
    LIMIT 0 , 3) as customOffers
    JOIN items ON items.OfferNum = offers.OfferNum

answered Nov 26 '19 at 13:27

Irakli

562
4
15

I need to retrieve rows of joined tabales, to process them via PHP. The 2nd script above yields this error:- You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT OfferNum FROM offers ORDER BY id ASC LIMIT 0 – Quest Nov 26 '19 at 15:20
I've already executed the very same queries with different table names and column names before writing this answer and it didn't trigger any error, as it seems your server recognizes comma in a different way I suppose, as your error is missing the part after comma, which is 3, and I think first you've to check what kind of separators are set in your regional settings, second thought is that you might have specific syntax for table and column name selection if you if you use a `phpmyadmin` for instance, and then you need ``SELECT `OfferNum` FROM `offers` ORDER BY `id` ASC LIMIT 0 , 3`` – Irakli Nov 26 '19 at 17:57
A normal SELECT statement works including LIMIT 0,3 and without the grave accent (`). This problem is most probably version-related. I am going to install MYSQL 5 aand see. WIll update later. – Quest Nov 26 '19 at 19:01
Could you please double check your regional settings first? What symbol is specified as a separator? To explain how to check it, I need to know what operational system are you running the server on. – Irakli Nov 26 '19 at 21:01
phpMyAdmin 2.6.1 + MySQL 4.0.23-nt on Windows xp. Old, I know but they've done the job so far. It's only this subquery that is not working! – Quest Nov 27 '19 at 03:52
Subqueries are not supported in versions lower than Mysql 4.1 https://stackoverflow.com/questions/5194036/subselect-sql-query-doesnt-work-on-mysql-4 – Quest Nov 27 '19 at 04:27
Wow, it is weird, never expected that outcome... But if you are able to check one thing for me, I am curious `Control Panel –> Clock, Language and Region –> Change the date, time, or number format` and there in `Numbers` tab you should have `Digit grouping` symbol and `Decimal symbol`, could you please tell me what symbols are assigned and to which properties? – Irakli Nov 27 '19 at 14:12
I can see this in Regional and Language Options Number: 123,456,789.00 Currency: $123,456,789.00 Time: 9:30:02 AM – Quest Nov 28 '19 at 09:34
Thank you for providing additional information, my suspicion is gone. – Irakli Nov 28 '19 at 10:18

score 1 · Answer 2 · answered Nov 26 '19 at 13:19

1

Move the LIMIT clause inside a subquery and join with it:

SELECT offers2.OfferNum, items.colour, items.availability
FROM (SELECT * FROM offers ORDER BY id LIMIT 0, 3) AS offers2
JOIN items ON items.OfferNum = offers2.OfferNum
ORDER BY ...

answered Nov 26 '19 at 13:19

Salman A

262,204
82
430
521

This yield the error:- #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM offers ORDER BY id LIMIT 0, 3) AS offers2 JOIN i – Quest Nov 26 '19 at 15:18
No syntax error here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c0a3ce8cb5d46579204d419e316cd13d – Salman A Nov 26 '19 at 15:26
On my production box, I use an old MYSQL version (4.0.23-nt). Can this be a problem? – Quest Nov 26 '19 at 15:42
Is there a way to check if MYSQL version 4 can do a table alias query ? I am only learning - not an expert. Thanks! – Quest Nov 26 '19 at 16:09
This does not use any fancy syntax. And MySQL 4 is sooooo old that they don't even maintain its documentation, sorry. – Salman A Nov 26 '19 at 18:21

score 0 · Answer 3 · answered Nov 27 '19 at 05:12

The problem was with MYSQL 4.0.

Subqueries are not supported in versions lower than Mysql 4.1 subselect sql query doesn't work on mysql 4

More details here: http://dev.mysql.com/doc/refman/4.1/en/subqueries.html

For a quick fix, I installed MYSQL 4.1, and both responses from Irakli Gigiberia and Salman A work.

Many thanks for your help.

MYSQL JOIN TWO TABLES (+ LIMIT based on first table)

3 Answers3