CODEWITHSUNDEEP
sqlsql-server-2005
user974047
user974047

Reputation: 2285

Select on records based on two column criterias

I would like to do an SQL query to select from the following table:

id    type    num
1     a       3
1     b       4
2     a       5
2     c       6

In the case where they have the same 'id' and be type 'a or b', so the result would look something like this:

id    type    num
1     a       3
1     b       4

Any one has any idea how that can be accomplished?

Upvotes: 2

Views: 118

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74277

Method 1:

select a.*
from some_table t
join some_table a on a.id = t.id and a.type = 'a'
join some_table b on b.id = t.id and b.type = 'b'

Method 2:

select *
from some_table t
where exists ( select *
               from some_table x
               where x.id   = t.id
                 and x.type = 'a'
             )
  and exists ( select *
               from some_table x
               where x.id   = t.id
                 and x.type = 'b'
             )

The first technique offers the possibilities of duplicate rows in the results set, depending on the cardinality of id and type. The latter is guaranteed to provide a proper subset of the table.

Either query, assuming you have reasonable indices defined on the table should provide pretty equivalent performance.

Upvotes: 0

invertedSpear
invertedSpear

Reputation: 11054

SELECT table1.* 
FROM table1,
(
  SELECT COUNT(*) as cnt, id
  FROM (
    SELECT * 
    FROM table1
    WHERE type = 'a' OR type = 'b'
  ) sub1
  GROUP BY id
  HAVING cnt > 1
)sub2
WHERE table1.id = sub2.id

Tested here: http://sqlfiddle.com/#!2/4a031/1 seems to work fine.

Upvotes: 1

Related Questions