Select from related tables

Question

So in my data base i have 2 tables related: The fields are id, name, price and a int so i know if they a all sold or not

fruits

|IDfruit| name  | price  | sold  |
|  1    |orange | 5      | 0
|  2    |apple  | 10     | 0
|  3    |grape  | 15     | 1
|  4    |lemon  | 7      | 1

primary key is IDfruit

images

|IDimage| url        | idfruit_image
| 1     | image1.png |     1      
| 2     | image2.png |     1
| 3     | image3.png |     2
| 4     | image4.png |     3    
| 5     | image5.png |     4
| 6     | image6.png |     4 
| 7     | image7.png |     4 

IDimage is primary key and idfruit_image is a foreign key that references IDfruit

The result i want is all fruits and the FIRST image of each fruit.

So what i've done is

select fruits.*, url , idfruit_image 
from fruits,images 
where IDfruit = idfruit_image;

This return all fruits and all images of each fruit, but i would like just one image of each fruit, how can i achieve this?

And what if i want everything from all sold fruits and just the first image of each one

Answer 1

Use GROUP BY to get one row per fruit, and an aggregation function to select one of the images.

SELECT f.*, MAX(url) AS url
FROM fruits AS f
LEFT JOIN images AS i ON f.idfruit = i.idfruit_image
GROUP BY f.idfruit