CODEWITHSUNDEEP
sqlfilter
skimchi1993
skimchi1993

Reputation: 229

SQL return records based on multiple columns values

Let's say I have the following data:

Number  TagValue
1       MLB
1       NFL
2       MLB
2       NFL
3       MLS
3       NFL
4       NFL

I want to return the following: 

Number  TagValue
1       MLB
1       NFL
2       MLB
2       NFL

The request is to look each number- tag value combo and see if it has BOTH MLB and NFL. If it does not I do not want to return it. A simple where clause will return: 

Number  TagValue
1       MLB
1       NFL
2       MLB
2       NFL
3       NFL
4       NFL

I do not want this.

Upvotes: 1

Views: 44

Answers (3)

sticky bit
sticky bit

Reputation: 37472

If you only want to show the records where the number has both tags and the actual tag of the record is one of the tags, then a straight forward solution would be a disjunction of two times an equal operation and an EXISTS.

SELECT t1.number,
       t1.tagvalue
       FROM elbat t1
            WHERE t1.tagvalue = 'MLB'
                  AND EXISTS (SELECT *
                                     FROM elbat t2
                                     WHERE t2.number = t1.number
                                           AND t2.tagvalue = 'NFL')
                   OR t1.tagvalue = 'NFL'
                      AND EXISTS (SELECT *
                                         FROM elbat t2
                                         WHERE t2.number = t1.number
                                               AND t2.tagvalue = 'MLB');

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use exists :

select t.*
from table t
where exists (select 1 from table t1 where t1.number = t.number and t1.tagvalue = 'MLB') and
      exists (select 1 from table t1 where t1.number = t.number and t1.tagvalue = 'NFL')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you want to return all numbers that have both codes, you can do:

select number
from t
where tagvalue in ('MLB', 'NFL')
group by number
having min(tagvalue) <> max(tagvalue);

I see little utility in returning multiple rows in this case.

If you want numbers with only one team, then:

select number, max(tagvalue)
from t
where tagvalue in ('MLB', 'NFL')
group by number
having min(tagvalue) = max(tagvalue);

Upvotes: 0

Related Questions