One product can have multiple images. Hence, there a one-to-many relationship from product to product_image and the product_image table has a foreign key that refers to the primary key of the product table.

I need to fetch a list of products with only a single image name in each row of the result set being retrieved regardless of the images being in the product_image table (even though there are no images for some of products).

The image name to be retrieved from the product_image table is generally the first image name in the product_image table after sorting each set of images for each product in ascending order. Something like the following.

prod_id prod_name prod_image
1 aaa aaa.jpg //The first image name in the product_image table after sorting images for prod_id in ascending order.
2 bbb bbb.jpg //Similar to the first case.
3 ccc - //No image(s) found in the product_image table
4 ddd - //Similar to the previous case.

The general join statement for these two tables would be something similar to the following.