shibbir ahmed February 2016

why mysqli left join return 1 more rows?

I have 2 tables called : 1) products 2) product_images and it's data is like that :

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's return 5 rows, that's correct :

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

But If I run this query it's return 6 rows, Why ? It's should be show 5 rows!

$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's return 1 more rows and How can I solved it? Thank You.

Actually I want to show all products with appropriate images.

Answers


Ravi Hirani February 2016

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

If you want to make p_id unique then use GROUP_BY in your query.

Write your query as below:-

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;

Hope it will help you :)


Mohsen Shakibafar February 2016

because you have 2 same p_id=1 in product_images


Khan Abdulrehman February 2016

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

Tutorials Point : Left join

Post Status

Asked in February 2016
Viewed 2,311 times
Voted 8
Answered 3 times

Search




Leave an answer