0

Use the e_store database Write a SELECT - JOIN query that displays the following table:

+----+--------------+--------+-------+
| id | product_name | price  | stars |
+----+--------------+--------+-------+
|  3 | PC           | 157.40 |     3 |
|  4 | Headphones   |  20.00 |     2 |
|  4 | Headphones   |  20.00 |     5 |
+----+--------------+--------+-------+
3 rows in set (0.02 sec)

I have the following code

 SELECT 
    products.id, products.name, price, reviews.stars 
    FROM products 
    JOIN reviews 
     ON products.id = reviews.product_id 
    WHERE products.id = 3 AND 4;

I am only getting products.id 3; how can I get 3 and 4?

2
  • 1
    id cannot be equal to 3 and 4 at the same time; it can be 3 or 4 though.
    – mustaccio
    Jun 17 '17 at 22:17
  • MySQL is fooling you because it allows for (a boolean condition) OR (a number). Any strongly typed programming language (except for a few like C) wouldn't let you write that. Most databases (PostgreSQL, for instance) wouldn't either. You can do different things... Check whether IN can work for you. Or just (a condition) OR (another one). In that particular case, you could even use BETWEEN.
    – joanolo
    Jun 17 '17 at 22:45
1

You need to use OR clause. Product 3 or product 4 - try this code:

SELECT 
  products.id, products.name, price, reviews.stars 
FROM products 
JOIN reviews 
 ON products.id = reviews.product_id 
WHERE products.id = 3 OR products.id = 4;

Basically condition is applied for each single row, and you cannot setup it to have contradictions like id = 3 AND id = 4 because you will get zero rows - that's why you need to use or instead.

Your condition is checking if id = 3 and 4 (4 is considered to be always true, so it's equals to check id = 3)

After adding or you will get statement you want, either id = 3, or id = 4.

For more conditions is good to use in: https://www.tutorialspoint.com/mysql/mysql-in-clause.htm

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.