How to use IN operator for satisfy all values inside?

Question

I am having one table specification_master with 3 fields.

1. spec_id

2. spec_value

3. spec_main_id

I am having below data inside table:

spec_id | spec_value | spec_main_id
11      | abc.com    | 10001
12      |            | 10001
13      |            | 10001
11      | xyz.com    | 10002
12      | google.com | 10002
13      | mozzila.com| 10002

Now I want data where spec_value is not blank:

And for this query is:

select spec_id, spec_value, spec_main_id 
from specification_master 
where spec_main_id IN (10001,10002) 
    AND spec_id IN (11,12) 
    AND spec_value != ''

By this I am getting data for 10001 also but I want data for 10002 only as per my query.

Desired result:

spec_id | spec_value | spec_main_id 
11      | xyz.com    | 10002 
12      | google.com | 10002

Answer 1

You are filtering on each record individually, so you are getting all of the spec_main_id of the records that have a spec_value filled (which includes getting the id duplicated). So you get the 10001 because in fact there are records where the 10001 has spec_value populated.

What it seems that you want: spec_main_id that has NO records with spec_value empty. To do that, since this id repeats itself for a group of records, you'll want to group by the id and get the MIN spec_value for any given group. If the group has a empty value, that will be the min value, if it doesn't, all values are populated.

select spec_main_id, MIN(spec_value) as min_spec_value
from specification_master 
where spec_main_id IN (10001,10002) AND spec_id IN (11,12) 
group by spec_main_id
having min_spec_value != ''

SQL fiddle: http://sqlfiddle.com/#!9/9072a2/3/0

Hope that helps.