CODEWITHSUNDEEP
mysqljoinlimit
Quest
Quest

Reputation: 119

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

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?

Upvotes: 2

Views: 2872

Answers (3)

Quest
Quest

Reputation: 119

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.

Upvotes: 0

Irakli
Irakli

Reputation: 627

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

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272386

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

Upvotes: 1

Related Questions