Re: Select Rows With Only One of Two Values

On 20 Jul 2012, at 18:15, Chris Angelico wrote:

> On Sat, Jul 21, 2012 at 1:53 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
>> The table has a Boolean indicator column with values of 0 or 1 for each
>> row in the table and another attribute column for parameter names. I need to
>> find all parameter names where the indicator value is only 0 for all rows of
>> that parameter. At least some of the parameters have both rows with 0 and
>> rows with 1 in the indicator attribute. I want to find all (any?) that have
>> only zeros.
>
> Try this:
>
> SELECT DISTINCT param FROM table WHERE indicator=0
> EXCEPT
> SELECT DISTINCT param FROM table WHERE indicator=1

I don't think the DISTINCT is necessary there, doesn't EXCEPT already return a distinct set, just like UNION (hence the existence of UNION ALL)?

It can also be written as a correlated subquery:

SELECT DISTINCT param FROM table t1 WHERE indicator = 0 AND NOT EXISTS (SELECT 42 FROM table t2 WHERE t2.param = t1.param AND indicator <> 0)

(Where 42 is just some placeholder value because the syntax requires it, any value will do but NULL might throw a spanner in the wheels)

Alban Hertroys

--
The scale of a problem often equals the size of an ego.