Why does mysqli left join return another 1 row?

Question

I have 2 tables: 1) products 2) product_images, and the data is as follows:

products table :
==============
ProductID   Title      model_number
-----------------------------------
1           title1     123
2           title2     124
3           title3     125
4           title4     126
5           title5     127

product_images
==============
pi_id    p_id   product_image
------------------------------
1        1      image1
2        2      image2
3        3      image3
4        4      image4
5        1      image1   

So, if I run this query, it will return 5 lines that are true:

$q=mysqli_query($conn, "SELECT * FROM products");
echo $n=mysqli_num_rows($q); // return 5 rows

But if I run this query, it will return 6 lines, why? It should contain 5 lines!

$searchQuery =  mysqli_query($conn, "SELECT products.ProductID,   
products.Title, products.model_number, product_images.product_image FROM 
products LEFT JOIN product_images ON products.ProductID =  
product_images.p_id ");

$isExist =  mysqli_num_rows($searchQuery); // return 6 rows

Can you tell me why it returns 1 more row and how can I solve it? Thanks.

In fact, I want to show all products with corresponding images.

Answer 1

You used LEFT JOIN . and you have two entries with p_id=1; So you have 6 lines.

p_id , GROUP_BY .

, : -

SELECT products.ProductID,   
products.Title, products.model_number, product_images.product_image FROM 
products LEFT JOIN product_images ON products.ProductID =  
product_images.p_id GROUP BY product_images.p_id;

, :)

Answer 2

2 p_id=1 product_images

Answer 3

LEFT JOIN returns all rows from the left table, even if there are no matches in the right table

Training point: Left join