The loop and if in MySQL

Question

In the table there are several columns, one is a varchar column called "Country", and another is a boolean column called "SomeFlag", I want to filter all the countries those have "SomeFlag" both 0 and 1 from records.
For example,

Record1: country is US, SomeFlag is 0 and some other values.  
Record2: country is US, SomeFlag is 1 and some other values.  
Record3: country is CA, SomeFlag is 1 and some other values.

So US is what I want to filter out, how should I construct this SQL query?
Thanks in advance.

I think you're saying to eliminate countries which have both row(s) with SomeFlag equal to 0 and other(s) with SomeFlag = 1. Right? — shawnt00
– shawnt00, Commented Feb 12, 2015 at 7:54
Yes buddy, that's it. I guess something went wrong with the expression, making it unclear to understand. — Sarotti
– Sarotti, Commented Feb 12, 2015 at 8:02
Well you didn't say the name of the table. Substitute that for T in any of my queries and I'll think you'll have it working. — shawnt00
– shawnt00, Commented Feb 12, 2015 at 8:04

shawnt00 · Accepted Answer · 2015-02-12 07:59:41Z

1

select Country from T
group by Country
having min(cast(SomeFlag as int)) = max(cast(SomeFlag as int))

or

select Country from T
group by Country
having count(distinct SomeFlag) = 1

or

select Country from T
where not exists (
    select 1 from T
    where SomeFlag = 0
) or not exists (
    select 1 from T
    where SomeFlag = 1
)

or

select Country from T
where Country not in (
    select Country from T
    group by Country
    having
            min(cast(SomeFlag as int)) = 0
        and max(cast(SomeFlag as int)) = 1
)

Do nulls in SomeFlag come into play?

edited Feb 12, 2015 at 7:59

answered Feb 12, 2015 at 7:48

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

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

Comments

Çağdaş Salur · Accepted Answer · 2015-02-12 07:44:39Z

0

"select * from table where (some_flag == '1' or some_flag == '0') and (country == 'US');"

answered Feb 12, 2015 at 7:44

Çağdaş Salur

1,4003 gold badges14 silver badges24 bronze badges

1 Comment

Sarotti Over a year ago

I think it should be a loop to get all the countries.

Dmytro Chasovskyi · Accepted Answer · 2015-02-12 07:50:48Z

0

It will be look like:

  SELECT *
    FROM Table
    WHERE (Table.SomeFlag == 0 OR Table.SomeFlag == 1) AND Table.Country == "US";

But if you don't want all information from the table use tags you want, except *.

edited Feb 12, 2015 at 7:50

answered Feb 12, 2015 at 7:47

Dmytro Chasovskyi

3,7018 gold badges54 silver badges100 bronze badges

Collectives™ on Stack Overflow

The loop and if in MySQL

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related