specify the correct element to pick in JOIN mysql i Have three tables:

---------------------
|      PRODUCTS     |
---------------------
| id |    product   |  
---------------------
| 1  | super prod 1 |
| 2  | super prod 2 |
| 3  | super prod 3 |
| 4  | super prod 4 |
| 5  | super prod 5 |
---------------------

--------------------------
|         images         |
--------------------------
| id |p_id|      image   |  
--------------------------
| 1  | 1  |  myimg01.jpg |
| 2  | 1  |  myimg02.jpg |
| 3  | 1  |  myimg03.jpg |
| 4  | 2  |  myimg04.jpg |
| 5  | 2  |  myimg05.jpg |
| 6  | 3  |  myimg06.jpg |
| 7  | 3  |  myimg07.jpg |
| 8  | 4  |  myimg08.jpg |
| 9  | 4  |  myimg09.jpg |
| 10 | 5  |  myimg10.jpg |
--------------------------


--------------------------
|          SALES         |
--------------------------
| id |p_id|      sale    |  
--------------------------
| 1  | 1  |      45      |
| 1  | 2  |      29      |
| 1  | 3  |      12      |
| 1  | 4  |      55      |
| 1  | 5  |      16      |
--------------------------


If i query all the tables with:

SELECT products.id,products.product, images.image, sales.sale 
FROM products 
LEFT JOIN images ON images.p_id = products.id 
LEFT JOIN sales ON sales.p_id = product.id 
GROUP BY product.id


i have these results:

---------------------------------------------------
| id |    product   |     image    |      sale    | 
---------------------------------------------------
| 1  | super prod 1 |  myimg03.jpg |      45      |
| 2  | super prod 2 |  myimg05.jpg |      29      |
| 3  | super prod 3 |  myimg07.jpg |      12      |
| 4  | super prod 4 |  myimg09.jpg |      55      |
| 5  | super prod 5 |  myimg10.jpg |      16      |
---------------------------------------------------


Please note: in the column 'image' i have the last image for a product. And my doubt starts here.

How i can obtain the FIRST image assigned to a product, using a single (an if we can: a simple) query?
Many thanks if you have a idea or clue about this.

Pd: Sorry mu poor english....
Your query is using grouping but you do not have any aggregation happening on any of your columns. By default, I believe MySQL will allow you to do this, but it is not supported or generally accepted as safe because MySQL will pick arbitrary values for your non-aggregated columns. (SEE: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) 

So assuming you want to group properly, I'm guessing you probably want a sum of sales for each product and returned with that aggregate, you want the "first" image.

What version of MySQL are you using? If you are using &lt; v8.0 then there's no "simple" way to effectively do this unless a few assumptions can be made. MySQL &lt; v8.0 lacks windowed functions / groupwise sorting (google "row_number over partition by") and I have not worked with that functionality in 8.0 yet so I cannot speak to that. 

If you are able to assume a few things though, you can probably get a working query.

Assuming your images table ID is auto_increment and you will always want the image with the lowest ID to be returned in this query, then the following should work:

mysql&gt; select p.id, p.product, i.image, sum(sale) from products p left join (select p_id, min(id) as minImageId from images group by p_id) as minI on p.id = minI.p_id left join images i on minI.minImageId = i.id left join sales s on p.id = s.p_id group by p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+
5 rows in set (0.00 sec)


Here is how I built the query up from the products table all the way to the finished query:

SELECT *
FROM products;
+----+--------------+
| id | product      |
+----+--------------+
|  1 | super prod 1 |
|  2 | super prod 2 |
|  3 | super prod 3 |
|  4 | super prod 4 |
|  5 | super prod 5 |
+----+--------------+


-- for each product, get the minimum image ID
SELECT *
FROM products
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON products.id = minI.p_id;
+----+--------------+------+------------+
| id | product      | p_id | minImageId |
+----+--------------+------+------------+
|  1 | super prod 1 |    1 |          1 |
|  2 | super prod 2 |    2 |          4 |
|  3 | super prod 3 |    3 |          6 |
|  4 | super prod 4 |    4 |          8 |
|  5 | super prod 5 |    5 |         10 |
+----+--------------+------+------------+


-- clean up query by removing a column we do not care to see
SELECT p.id,
       p.product,
       minI.minImageId
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id;
+----+--------------+------------+
| id | product      | minImageId |
+----+--------------+------------+
|  1 | super prod 1 |          1 |
|  2 | super prod 2 |          4 |
|  3 | super prod 3 |          6 |
|  4 | super prod 4 |          8 |
|  5 | super prod 5 |         10 |
+----+--------------+------------+

-- join in image table using the min image ID we found ealier
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id;
+----+--------------+------------+-------------+
| id | product      | minImageId | image       |
+----+--------------+------------+-------------+
|  1 | super prod 1 |          1 | myimg01.jpg |
|  2 | super prod 2 |          4 | myimg04.jpg |
|  3 | super prod 3 |          6 | myimg06.jpg |
|  4 | super prod 4 |          8 | myimg08.jpg |
|  5 | super prod 5 |         10 | myimg10.jpg |
+----+--------------+------------+-------------+

-- bring in and aggregate our sales data. if you do not put an aggregate function on the sale value, mysql will pick a value for you arbitrarily
SELECT p.id,
       p.product,
       minI.minImageId,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+------------+-------------+-----------+
| id | product      | minImageId | image       | sum(sale) |
+----+--------------+------------+-------------+-----------+
|  1 | super prod 1 |          1 | myimg01.jpg |        45 |
|  2 | super prod 2 |          4 | myimg04.jpg |        29 |
|  3 | super prod 3 |          6 | myimg06.jpg |        12 |
|  4 | super prod 4 |          8 | myimg08.jpg |        55 |
|  5 | super prod 5 |         10 | myimg10.jpg |        16 |
+----+--------------+------------+-------------+-----------+


-- clean up query by removing a column we do not care about
SELECT p.id,
       p.product,
       i.image,
       sum(sale)
FROM products p
LEFT JOIN
  (SELECT p_id,
          min(id) AS minImageId
   FROM images
   GROUP BY p_id) AS minI ON p.id = minI.p_id
LEFT JOIN images i ON minI.minImageId = i.id
LEFT JOIN sales s ON p.id = s.p_id
GROUP BY p.id;
+----+--------------+-------------+-----------+
| id | product      | image       | sum(sale) |
+----+--------------+-------------+-----------+
|  1 | super prod 1 | myimg01.jpg |        45 |
|  2 | super prod 2 | myimg04.jpg |        29 |
|  3 | super prod 3 | myimg06.jpg |        12 |
|  4 | super prod 4 | myimg08.jpg |        55 |
|  5 | super prod 5 | myimg10.jpg |        16 |
+----+--------------+-------------+-----------+