0

MySQL results are returning duplicate products from a "Product" table because multiple items have the same "image.id_node" value under an "Images" table. Any way I can edit this so it only returns one product per corresponding 'image.id_node'? Preferably by finding the one with the highest ID value?

"SELECT * 
  FROM `product` 
    left join image 
      on product.id_node = image.id_node 
  WHERE `product_publish` = 1 
    AND image.main = 1 
    AND `product_type_item`=$type_item 
    AND product_status = 1 
  order by product_position ASC;"
pala_
  • 8,901
  • 1
  • 15
  • 32
Rick
  • 21

1 Answers1

0

You can use a subquery for this, which determines the max(product.id) per id_node.

select *
  from (
    select max(id) id
      from `product`
      group by `id_node`
  ) q
  inner join `product` p
    on q.id = p.id
  left join image i
    on p.id_node = i.id_node
  where `product_publish` = 1
    and i.main = 1
    and `product_type_item` = $type_item
    and product_status = 1
  order by product_position asc
pala_
  • 8,901
  • 1
  • 15
  • 32
  • i doubt that query performs as you expect - at least not reliably.. the `where` clause is entirely redundant. – pala_ May 07 '15 at 15:26