Thread: Select Rows With Only One of Two Values

   What I thought would be a simple, single table select keeps eluding me.
I've looked in Rick van der Laans' book and the Joe Celko books here and
have not learned how to write the query.

   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.

   I have tried various flavors of this non-working syntax:

SELECT DISTINCT(param) from table WHERE indicator = 0 and indicator <> 1
order by param;

and have not found the correct way of writing this either directly or as a
correlated query. The proper syntax must not be complicated and I would
appreciate learning how to write it.

Rich

On Sat, Jul 21, 2012 at 1:53 AM, Rich Shepard <rshepard@appl-ecosys.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

You'll get a list of rows with indicator 0, and then remove from that
list any that are also found in the second query. What's left is the
rows that have only indicator 0.

Chris Angelico

On Sat, 21 Jul 2012, Chris Angelico wrote:

> Try this:
>
> SELECT DISTINCT param FROM table WHERE indicator=0
> EXCEPT
> SELECT DISTINCT param FROM table WHERE indicator=1

Chris,

   Thank you. I knew it was simple, and I've not before used the EXCEPT
condition.

Very much appreciate,

Rich

On Sat, Jul 21, 2012 at 2:21 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> On Sat, 21 Jul 2012, Chris Angelico wrote:
>
>> Try this:
>>
>> SELECT DISTINCT param FROM table WHERE indicator=0
>> EXCEPT
>> SELECT DISTINCT param FROM table WHERE indicator=1
>
>
> Chris,
>
>   Thank you. I knew it was simple, and I've not before used the EXCEPT
> condition.
>
> Very much appreciate,
>
> Rich

No probs! SQL, like many other languages, allows you to combine its
primitives in some fairly complex ways. I don't know how hard it'd be
to make it work on Postgres, but here's an epic piece of SQL
awesomeness:

http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx

ChrisA

Chris Angelico <rosuav@gmail.com> wrote:

> No probs! SQL, like many other languages, allows you to combine its
> primitives in some fairly complex ways. I don't know how hard it'd be
> to make it work on Postgres, but here's an epic piece of SQL
> awesomeness:
>
> http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx

http://wiki.postgresql.org/wiki/Mandelbrot_set


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Chris Angelico wrote on 20.07.2012 18:25:
> I don't know how hard it'd be to make it work on Postgres, but here's
> an epic piece of SQL awesomeness:
>
> http://thedailywtf.com/Articles/Stupid-Coding-Tricks-The-TSQL-Madlebrot.aspx

That has already been done - and much cleaner I think ;)

https://wiki.postgresql.org/wiki/Mandelbrot_set

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

> On Sat, Jul 21, 2012 at 1:53 AM, Rich Shepard <rshepard@appl-ecosys.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
existenceof 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.paramAND indicator <> 0) 

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

Alban Hertroys

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

On Sat, Jul 21, 2012 at 6:52 AM, Alban Hertroys <haramrae@gmail.com> wrote:
> I don't think the DISTINCT is necessary there, doesn't EXCEPT already return a distinct set, just like UNION (hence
theexistence of UNION ALL)? 
>
> It can also be written as a correlated subquery:

Oops, yes. I usually use UNION ALL and friends, and IMHO that "ALL"
keyword is one of SQL's weirder warts. Most of the time, a wordier
query involves more work (SELECT DISTINCT vs SELECT), but in this odd
instance, it's the other way around.

And yes, it can. Not sure whether the subquery or EXCEPT notation is
more readable. Much of a muchness, really.

ChrisA