CODEWITHSUNDEEP
sql
kb_
kb_

Reputation: 1335

Intersect on just one column, return others

I am trying to do an intersection on just one column (map_id), but also return another column (attr):

SELECT map_id, attr 
FROM Attr
WHERE attr_id = 0 AND val = '123'
INTERSECT
SELECT map_id, attr
FROM Attr
WHERE attr_id = 20 AND val = '456'

However, if the pair map_id and attr don't match, the intersection doesn't work (obviously). How do I do the intersection on map_id, but return the list of attrs? It eventually becomes part of a col In () clause of a larger query.

For example:

---------------------------------
Attr_Id    Attr    Val    Map_Id
---------------------------------
0          '1'     '123'    1
0          '2'     '123'    2
10         '3'     '123'    1
10         '4'     '123'    2
20         '5'     '456'    1
20         '6'     '456'    2
30         '7'     '456'    1
30         '8'     '456'    2

I want it to return 

----------------
Attr_Id    Attr   
----------------
0          '1'   
20         '5'

Upvotes: 1

Views: 4748

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You seem to want to find the matching attributes for a map_id when the map_id has both attributes. There are different ways to approach this query. Here is one way:

select a.attr_id, a.attr
from Attr a
where (attr_id = 0 and val = '123' or
       attr_id = 20 and val = '456'
      ) and
      2 = (select count(distinct a2.attr_id)
           from attr a2
           where a2.map_id = a.map_id and
                (a2.attr_id = 0 and a2.val = '123' or
                 a2.attr_id = 20 and a2.val = '456'
                )
          )

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select a.attr_id, a.attr
  from (select map_id
          from attr
         where attr_id = 0
           and val = '123'
        intersect
        select map_id
          from attr
         where attr_id = 20
           and val = '456') v
  join attr a
    on v.map_id = a.map_id

The reason it returns every record is because your 2 queries both produce MAP_ID values of 1 and 2. Thus, every single row of the table with a MAP_ID of 1 or 2 will be returned. This does not match your expected output but matches your explanation.

Upvotes: 1

Related Questions