Dfyjkt

I have two tables that one stores advertisements (ads) and other stores images (images). images usually contain more than one images per each records in ads.

ads table

+------------+------------+
|ad_id |title |
+------------+------------+
|1 |sample1 |
+------------+------------+
|2 |sample2 |
+------------+------------+

images table

+------------+------------+
|image_id |image |
+------------+------------+
|1 |1.jpg |
+------------+------------+
|1 |2.jpg |
+------------+------------+
|2 |3.jpg |
+------------+------------+

What I want to do is make a list of ads with one image per each record in ads and currently using following sql query.

SELECT a.`title`, i.`image`
FROM ads a
LEFT JOIN `images` i ON i.`id` = a.`id` 

but this returns me three records instead of two ads, which contains duplicate records from ads as images contains two records of images.

How do I limit only one image per one record in the ads.

Any help would be really appreciated. TIA

PS:
current output is like

sample1, 1.jpg
sample1, 2.jpg
sample2, 3.jpg

But I'm expecting

sample1, 1.jpg
sample2, 3.jpg

or

sample1, 2.jpg
sample2, 3.jpg

LEFT JOIN means will be on the table which on the left side by the ON condition but it will be all match by id = 1 or id = 2

You can try to use aggregate function MIN or MAX oni.image` column to make your expectation.

If you want to get 1.jpg you can use MIN otherwise use MAX

CREATE TABLE ads(
 ad_id INT,
 title VARCHAR(50)
);


INSERT INTO ads VALUES (1,'sample1');
INSERT INTO ads VALUES (2,'sample2');

CREATE TABLE images(
 image_id INT,
 image VARCHAR(50)
);


INSERT INTO images VALUES(1,'1.jpg');
INSERT INTO images VALUES(1,'2.jpg');
INSERT INTO images VALUES(2,'3.jpg');

Query #1

SELECT a.`title`,MIN(i.`image`)
FROM ads a
LEFT JOIN `images` i ON i.`image_id` = a.`ad_id` 
GROUP BY a.`title`;

| title | MIN(i.`image`) |
| ------- | -------------- |
| sample1 | 1.jpg |
| sample2 | 3.jpg |

View on DB Fiddle

The simplest way is probably a correlated subquery:

SELECT a.title,
 (SELECT i.image
 FROM image` i 
 WHERE i.id = a.id 
 LIMIT 1
 ) as image
FROM ads a;

This returns an arbitrary image. You can add an ORDER BY to the subquery to get a particular image, such as:

• 
  ORDER BY rand() to get a random image


• 
  ORDER BY i.id to get the smallest id
  


• 
  ORDER BY i.createDate DESC to get the newest one


• and so on

SELECT OG.tittle as Title, 
 OG.image as Image
FROM 
(
SELECT a.title as tittle, 
 i.image as image,
 row_number() over (Partition BY i.image_id ORDER BY i.image_id ASC ) as rn
FROM ads a
LEFT JOIN images i ON i.id = a.id
) OG
WHERE rn =1

This query will only display one image per one record based on row number based filtering .

For more info on joins click here https://medium.com/@cdaniel7/run-down-of-sql-joins-434d9d03f2d