Rahul Kaushik Rahul Kaushik - 3 months ago 4
MySQL Question

fetch data from table where if condition is true all related rows hides

i have a table as below:

--------------------------------------
id | product_id | value
--------------------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1
5 | 2 | 2


now i want that i select data and if in where condition, value!=3 then result should not have rows where product id is 1 because it contains value = 3

hope you understand.

Answer

SELECT * FROM mytable WHERE product_id NOT IN (SELECT product_id FROM mytable WHERE value=3)

There is a nested query in this. In the nested query you find all product_id's which has value 3, this returns 1. Then in your first query you find the product that are NOT in the returned query. So product_id's which are not 1.

This query returns rows with id's 4 and 5.

Comments