0

I have the following data that contains 2 different TBL_ID - 337448 and 8612

"TBL_ID","PARAM_KEY","PARAM_VALUE"
 337448,"comment","CHANGE DUE"
 337448,"transient_lastDdlTime","1505760292"
 8612,"COLUMN_STATS_ACCURATE","true"
 8612,"numFiles","1"
 8612,"numRows","5294"
 8612,"rawDataSize","-1"
 8612,"totalSize","113217"
 8612,"transient_lastDdlTime","1452191300"

I need to query all TBL_ID that do not contain "numRows" What I have tried:

SELECT * FROM TABLE_PARAMS 
WHERE PARAM_KEY NOT IN ('numRows')

The results:

"TBL_ID","PARAM_KEY","PARAM_VALUE"
 337448,"comment","CHANGE DUE"
 337448,"transient_lastDdlTime","1505760292"
 8612,"COLUMN_STATS_ACCURATE","true"
 8612,"numFiles","1"
 8612,"rawDataSize","-1"
 8612,"totalSize","113217"
 8612,"transient_lastDdlTime","1452191300"

It did remove the column with numRows but I need to remove the whole TBL_ID (8612)

TheNewGuy
  • 559
  • 1
  • 10
  • 27
  • 1
    I believe you are using a wrong set of query there. IN operator is used to find data out of a result set. For example is 2 IN {2, 3, 4}. – Sudeep Devkota Dec 15 '17 at 21:45

2 Answers2

2
NOT IN ('numRows')

is basically a not equals

<> 'numRows'

Normally, one uses a NOT IN with more than one value to simplify writing a whole bunch of 

mycol <> 'a' and mycol <> 'b' and mycol <> 'c'

and just write

mycol NOT IN ('a', 'b', 'c')

or to use a subquery like

mycol NOT IN (SELECT badcols FROM table)

Your usage seems like a case where NOT EXISTS is going to be more useful than NOT IN.

SELECT * 
FROM TABLE_PARAMS t1
WHERE NOT EXISTS (SELECT * FROM TABLE_PARAMS t2 WHERE t2.tbl_id = t1.tbl_id and t2.param_key<>'numFiles')

Or you could do the same thing with an outer join like

SELECT t1.*
FROM TABLE_PARAMS t1 left outer join TABLE_PARAMS t2
  on t1.tbl_id = t2.tbl_id and t2.param_key='numFiles'
WHERE t2.tbl_id IS NULL
EdmCoff
  • 3,506
  • 1
  • 9
  • 9
2

Your question is a little ambiguous. IN operator is used to find data out of a result set. For example is 2 IN {2, 3, 4}. 

SELECT TBL_ID FROM  TABLE_PARAMS
WHERE PRAMAR_KEY='numRows'

If you are trying to not show any of the ID that is associated with numRows, then you must get the ID that you would like to filter first and user NOT EQUALS to. For example, if you are trying to remove 8612 out of your set because it is the ID of Param_Key ='numRows' AND IF YOU ARE TRYING TO USE IN OPERATOR, then

SELECT * FROM TABLE_PARAMS
WHERE TBL_ID NOT IN (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='numRows')
Sudeep Devkota
  • 189
  • 1
  • 1
  • 11