MySQL Query creation help, multiple inner joins and specifying rows to return

[mitel]

Given the below 3 database tables I am trying to construct a SQL query that will give me the following result:

customer_favourites.cust_id
customer_favourites.prod_id OR product.id
product.code
product.product_name
product.hidden
product_ section.section_id (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.catpage (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.relative_order (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)

I currently have....

SELECT customer_favourites.cust_id, customer_favourites.prod_id, product.code, product.product_name, product.hidden, product_section.section_id, product_section.relative_order, product_section.catpage  
FROM customer _favourites  
INNER JOIN product ON customer_favourites.prod_id = product.id  
INNER JOIN product_section ON product_section.product_code = product.code  
WHERE `cust_id` = '17' AND `hidden` = '0' GROUP BY `code`  
ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC LIMIT 0,30

This gives me what I want but only sometimes, at other times it randomly selects any row from the product_section table.

I was hoping that by having the row I want as the last row (most recent added) in the product_section table then it would select that row by default but it is not consistent.

Somehow, I need to be able to specify which row to return in the product_section table, it needs to be the row with the lowest section_id value or it should by the last row (most recent).

Pulling my hair out so any help is gratefully received.

 

customer_favourites

id     cust_id   prod_id
70    4            469

product

id        code           product_name    hidden
469    ABC123     My Product        0

product_section

id                  section_id      catpage    product_code    relative_order     recommended
44105         19                   232            ABC123             260                    1
44106          3                     125            ABC123              87                      1
44107          2                     98              ABC123             128                     1
44108          1                    156             ABC123             58                        0

 

[Barand]

Imagine you had another table that contained

 

product_code | latest_id

 

for every product code. You could then join the product_section table to that matching on  the code and the id to get the latest. Using a table subquery gives you that table.

SELECT 
    customer_favourites.cust_id
    , customer_favourites.prod_id
    , product.code
    , product.product_name
    , product.hidden
    , product_section.section_id
    , product_section.relative_order
    , product_section.catpage  
FROM customer _favourites  
INNER JOIN product ON customer_favourites.prod_id = product.id  
INNER JOIN product_section ON product_section.product_code = product.code
INNER JOIN (
    SELECT product_code
        , MAX(id) as id
        FROM product_section
        GROUP BY product_code
    ) as latest ON product_section.product_code = latest.product_code
            AND product_section.id = latest.id  
WHERE `cust_id` = '17' AND `hidden` = '0'  
ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC 
LIMIT 0,30