'SQL statement not giving me desired output when i do not have a certain column

I am trying to retrieve these values from mysql table using this statement in my node.js

          SELECT product.name, product.image, product.description, product.brand, product.price, review.rating       
            FROM product INNER JOIN review
            ON product.productid = review.productid
            WHERE product.name = ?

However, products that do not have a review assigned to them doesnt give me the desired output.

eg product 1 has a review assigned to it as shown in the review table and will give me my data, but product 2 does not have a review assigned to it and as a result, i do not get my data enter image description here

Review Table

enter image description here

How do I fix this so as to achieve my desired output regardless of if the product has a review assigned to it.

Thank you

mysql


Solution 1:[1]

You are doing an inner join, which only returns rows where the productID matches in both tables. You need to do a left join so that if the productID does not exist in the review table, the matching row in the product table will still be returned.

Solution 2:[2]

Kindly use LEFT JOIN instead.

SELECT product.name, product.image, product.description, product.brand, product.price, review.rating       
FROM product LEFT JOIN review
ON product.productid = review.productid
WHERE product.name = ?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tyler Liu
Solution 2 ruleboy21

Related Questions