Comparing two or more values in sql

Question

I have a table which has the following.

 fieldmapid | fieldid
------------+---------
       2010 |     180
       2012 |      90
       2012 |      92
       2020 |      90
       2020 |      92
       2020 |      95
       20005 |      90
       20005 |      92

I want to write a query which first gets the fieldid's of the fieldmapid(assume it's 2012). And then i want to select the fieldmapid's which have the same fieldid's as the ones in 2012. That means, I need fieldmapid's which have only 90 and 92 as their fieldid's and nothing else. so i want 20005 and 2012 as the output of the query. because these are the only two fieldmapid's which have only 90 and 92 as their fieldid's.

You need to provide the desired result set and what you've tried. — Kermit, May 29 '14 at 15:52

Charvee Shah · Answer 1 · 2014-05-29T14:24:45.707

1

Try this.May be it will help you.

select f.fieldmapid from Field f
LEFT JOIN Field f1 on f1.fieldid=f.fieldid and 

 (f1.fieldmapid=2012) 
group by f.fieldmapid
having 
count(f.fieldmapid)=count(f1.fieldmapid);

Demo link : http://sqlfiddle.com/#!2/b72e05/3

edited May 29 '14 at 14:24

answered May 29 '14 at 14:19

Charvee Shah

730
1
6
21

score 0 · Answer 2 · answered May 29 '14 at 13:34

This is an example of a "set-within-sets" subquery. You can solve it by doing:

select t.fieldmapids
from table t left outer join
     (select fieldid
      from table t2
      where fieldmapid = 2012
     ) f2012
     on f2012.fieldmapid = t.fieldmapid
group by t.fieldmapids
having count(*) = count(f2012.fieldmapid) and
       count(*) = (select count(*) from table t2 where fieldmapid = 2012);

The first condition in the having clause checks that all the fields for a given fieldmapid match at least one field for 2012. The second verifies that the number of fields on fieldmapid matches the number on 2012.

Can this really not be simplified? – Neil May 29 '14 at 13:37 — Neil, May 29 '14 at 13:37

Comparing two or more values in sql

2 Answers2