CODEWITHSUNDEEP
sqlgoogle-bigquery
Manisha29
Manisha29

Reputation: 1

Excluding a set of data from table

I'm trying to filter out a certain value from my "Fruit_Table" table:

    Product     Product Type 

     Apple         Fruit 
     Apple         Fruit 
     Apple         Fruit_2
     Apple         Fruit_2 
     Banana        Fruit 
     Banana        Fruit
     Pear          Fruit
     Pear          Fruit 
     Pear          Fruit_2 
     Pear          Fruit_2

So I want to remove any rows where Apple and Pear = Fruit.

Ideally my table would then look like this: 

    Product     Product Type 


     Apple         Fruit_2
     Apple         Fruit_2 
     Banana        Fruit 
     Banana        Fruit 
     Pear          Fruit_2 
     Pear          Fruit_2

I've tried the Where NOT IN clause:

Select *
from Fruit_Table
WHERE Product NOT IN ( 'Apple' , 'Pear') AND Product_Type = "Fruit"

However this gets rid of all the Apple and Pear values in my table, i.e I am only left with "Banana, Fruit".

I tried IF statement however not too sure how this would work. In an ideal world I would write; 

IF Product = Apple or Pear AND Product_Type = Fruit then EXCLUDE

^ Don't think that exists, but something along those lines!

If someone could help that would be great.

Thanks, Manisha

Upvotes: 0

Views: 23

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.Fruit_Table` AS (
  SELECT 'Apple' Product, 'Fruit' Product_Type UNION ALL 
  SELECT 'Apple', 'Fruit' UNION ALL 
  SELECT 'Apple', 'Fruit_2' UNION ALL
  SELECT 'Apple', 'Fruit_2' UNION ALL 
  SELECT 'Banana', 'Fruit' UNION ALL 
  SELECT 'Banana', 'Fruit' UNION ALL
  SELECT 'Pear', 'Fruit' UNION ALL
  SELECT 'Pear', 'Fruit' UNION ALL 
  SELECT 'Pear', 'Fruit_2' UNION ALL 
  SELECT 'Pear', 'Fruit_2' 
)
SELECT *
FROM `project.dataset.Fruit_Table`
WHERE NOT (Product IN ('Apple', 'Pear') AND Product_Type = 'Fruit')

with result 

Row Product Product_Type     
1   Apple   Fruit_2  
2   Apple   Fruit_2  
3   Banana  Fruit    
4   Banana  Fruit    
5   Pear    Fruit_2  
6   Pear    Fruit_2

Upvotes: 1

Related Questions