Search code examples
sqlselectjdbctogglederby

SQL select items that span entire range of attribute

The title might sound a little unclear, hopefully my description will be clearer! Here is what I am trying to do. I have a table with 3 columns, id, value, and a binary attribute. i want to select items from the table that have the same name, but opposing binary attributes, so for example

ID|Value     |Toggle
====================
0 |noun      |no
1 |verb      |no
2 |noun      |yes
3 |adjective |yes
4 |article   |no
5 |noun      |no
6 |adjective |yes
7 |verb      |no
8 |article   |yes

on this table, the returned records would be 

ID|Value     |Toggle
====================
0 |noun      |no
2 |noun      |yes
4 |article   |no
5 |noun      |no
8 |article   |yes

all noun records and all article records would be returned because there exist noun records that have no and yes, and article records that have no and yes.

adjective would not be returned because both records have a toggle of yes, and verb would not be returned because both records have a toggle of no. Thanks for reading!

PLEASE ask me any follow up questions if my description is still not clear. I am using Derby with JDBC, if it matters.

Solution

  • This may work for you, I would be interested to know how quick/slow this would be (if you have an index on tog, it would be very fast)....I assume the table name is t and the column value is val and the column toggle is tog :)

    SELECT * FROM T 
WHERE  tog = 'yes' and val in (select val from t where tog='no')

UNION 

SELECT * FROM T 
WHERE  tog = 'no'  and val in (select val from t where tog='yes')