1

I have a complex query, but here is the essence of what I'm trying to do

In a table looking like this:

 Col1 Col2 Bill 0 Bill 0 Bill 0 Bill 1 Bill 1 John 1 John 1 Jonh 1

The above table has 2 columns. I am trying to query the rows that has '1' in all the rows in column 2. Since 'Bill' rows with '0' and '1', bill must be excluded. Subquery in the WHERE statement gives more than one result, and doen't work.

SELECT t1.Col1 FROM Table1 t1 WHERE t1.Col1 <> (SELECT t1.Col1 FROM Table1 t1 WHERE t1.Col2 <> '0')

Using a form of loop statement in the query would bring a whole new level of headache to my project so I hope any smart person can assist me in my quest.

Improve this question
1
  • Just to clarify, from the sample data above should the query return "John" or nothing? Commented Mar 6, 2012 at 13:19

4 Answers 4

Reset to default
4

There are a number of approaches. Each one has its merits depending on your RDBMS and whatever you find easiest to read. See here for more details about performance and check your execution plans or do some testing to obtain the solution that best suits your needs.

SELECT * FROM Table t1 WHERE t1.Col1 NOT IN (SELECT t1.Col1 FROM Table1 t1 WHERE t1.Col2 <> '0')

or

SELECT t1.* FROM Table t1 LEFT JOIN ( SELECT Col1 FROM Table WHERE Col2 <> '0' ) Exc ON exc.Col1 = t1.Col1 WHERE Exc.Col1 IS NULL

or

SELECT * FROM Table t1 WHERE NOT EXISTS ( SELECT 1 FROM Table t2 WHERE t2.Col2 <> '0' AND t1.Col1 = t2.Col1 )
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

So substituting '<>' with 'NOT IN' makes all the difference? I'm eager to try that!
3 
SELECT t1.Col1 FROM Table1 t1 WHERE NOT EXISTS (SELECT t2.Col1 FROM Table1 t2 WHERE t2.col2 = 0 AND t2.col1 = t1.col1)
Improve this answer

2 Comments

Comparing the nested query to the main query? Nice! But will that use resources exponentially when used in more complicated queries?
A good place to read up on the implications of exists is here: sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in
1 
SELECT Col1 FROM YourTable WHERE Col2 = 1 EXCEPT SELECT Col1 FROM YourTable WHERE Col2 <> 1;
Improve this answer

1 Comment

no correct answer mark for this, it solved everything?
0

Try:

SELECT Col1 FROM Table1 GROUP BY Col1 HAVING COUNT(DISTINCT Col2)=1 and MAX(Col2)='1'
Improve this answer

4 Comments

I don't think this would work correctly, although the OP's query does not match his question so it is hard to say for definite. If there were another row e.g col1 = 'foo', col2 = 0. Your query would return both John and Foo, whereas based on the question it should only return John, and based on the query in the question it should only return Foo. I think you'd need HAVING COUNT(CASE WHEN Col2 != '0' THEN 1 END) = 0 instead of HAVING COUNT(DISTINCT Col2) = 1. Or you could use HAVING COUNT(Col2) = COUNT(CASE WHEN Col2 = '0' THEN 1 END)`
Oops - I missed the requirement to return only Col2='1' (instead of just any single value). Query updated accordingly.
Not return Col2='1', but exclude Bill and only return John because some of Bill's Col2 has the value of '0'.
@user1251900: From your question: "I am trying to query the rows that has '1' in all the rows in column 2."

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.