SQL return records based on multiple columns values

Question

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.

@GordonLinoff: Record 3 in the original is MLS not MLB. He just wants to exclude 3 and 4. — Robert Harvey
– Robert Harvey, Commented Aug 24, 2018 at 15:10

sticky bit · Accepted Answer · 2018-08-24 15:32:00Z

1

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');

answered Aug 24, 2018 at 15:32

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-08-24 15:26:46Z

1

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')

edited Aug 24, 2018 at 15:26

answered Aug 24, 2018 at 15:13

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

2 Comments

skimchi1993 Over a year ago

i just tried this and still returns results that have just one of MLB and NFL?

Yogesh Sharma Over a year ago

@skimchi1993. . . You need two exists.

Gordon Linoff · Accepted Answer · 2018-08-24 15:10:44Z

0

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);

answered Aug 24, 2018 at 15:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL return records based on multiple columns values

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related