mitel Posted October 3, 2014 Share Posted October 3, 2014 Given the below 3 database tables I am trying to construct a SQL query that will give me the following result:customer_favourites.cust_idcustomer_favourites.prod_id OR product.idproduct.codeproduct.product_nameproduct.hiddenproduct_ 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,30This 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_favouritesid cust_id prod_id70 4 469 productid code product_name hidden469 ABC123 My Product 0 product_sectionid section_id catpage product_code relative_order recommended44105 19 232 ABC123 260 144106 3 125 ABC123 87 144107 2 98 ABC123 128 144108 1 156 ABC123 58 0 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 3, 2014 Share Posted October 3, 2014 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.