title: MySQL SELECT, JOIN, GROUP BY query optimizing
body: <p>I am trying to select DISTINCT products from within categories with category ids (1, 5, 12), ORDERED by cat_order + prod_order from MySQL database</p>
  
  <p><strong>The problem:</strong></p>
  
  <p>if a product is found in more than 1 category I need to show the first result,</p>
  
  <p>ie: product number 1 is assigned to categories 1 and 5, I need to display product number 1 from category 1 along with its prod_order and skip the listing in category 5, </p>
  
  <ul>
  <li>essentually I need to display all products from category 1, than move on to category 5 and display all products from there, where product id was not shown previously, and move on to another category in the list (12)</li>
  </ul>
  
  <p>if I run something like:</p>
  
  <pre><code>SELECT
      prod_to_cat.prod_id AS prod_to_cat_prod_id,
      prod_to_cat.prod_order AS prod_to_cat_prod_order,
      prod_to_cat.cat_id AS prod_to_cat_cat_id,
      prod_to_cat.cat_order AS prod_to_cat_cat_order,
      products.id,
      products.name
  
  FROM
      prod_to_cat, products
  
  WHERE
      prod_to_cat.prod_id = products.id
  AND prod_to_cat.cat_id IN (1, 5, 12)
  GROUP BY 
      prod_to_cat.prod_id
  ORDER BY
      prod_to_cat_cat_order ASC,
      prod_to_cat_prod_order DESC
  </code></pre>
  
  <p>I get inconsistent results (product 1 will not be selected from the first category in the list), that is why I opted to select without "GROUP BY prod_id" and wrap that with another select which than groups by prod_id.</p>
  
  <p>like so:</p>
  
  <pre><code>SELECT
      prod_to_cat_prod_id,
      prod_to_cat_prod_order,
      prod_to_cat_cat_id,
      name
  FROM
      (
          SELECT
              prod_to_cat.prod_id AS prod_to_cat_prod_id,
              prod_to_cat.prod_order AS prod_to_cat_prod_order,
              prod_to_cat.cat_id AS prod_to_cat_cat_id,
              prod_to_cat.cat_order AS prod_to_cat_cat_order,
              products.id,
              products.name
          FROM
              prod_to_cat, products
          WHERE
              prod_to_cat.prod_id = products.id
          AND prod_to_cat.cat_id IN (1, 5, 12)
          ORDER BY 
              prod_to_cat_cat_order ASC,
              prod_to_cat_prod_order DESC
      ) AS prod
  GROUP BY
      prod_to_cat_prod_id
  ORDER BY
      prod_to_cat_cat_order ASC,
      prod_to_cat_prod_order DESC
  LIMIT 0, 10;
  </code></pre>
  
  <p><strong>What I am trying to do:</strong></p>
  
  <p>I am trying to find a more efficiant way to do this.</p>
  
  <p>Table structure:</p>
  
  <pre><code>prod_to_cat:
  
  prod_id | cat_id | cat_order | prod_order   |
      1        1          1           2
      2        1          1           0
      3        1          1           0
      1        5          2           4
      4        5          2           0
  
  
  products:
  
  
  id  |   name |  descr   |   price   |
   1    name_1
   2    name_2
   3    name_3
   4    name_4
  </code></pre>
  
  <p>each product can be in any number of categories, for example product id 1 is in categories id 1 and 5 in the example above.</p>
  
  <p>Thanks a lot for any replies.
  Pasha</p>