CODEWITHSUNDEEP
mysqlsql
Abhishek
Abhishek

Reputation: 7045

How to do group by chunk wise in SQL with negative condition?

I've asked a question here. Now, I need reverse output of it. I've following table, My_Table, with structure as follows,

Name   | Address
----------------
Test1  | abc_123
Test1  | abc_456
Test2  | xyz_123
Test1  | xyz_123
Test2  | xyz_456
Test3  | abc_123
Test4  | xyz_123
Test1  | xyz_456
Test3  | xyz_123
Test4  | xyz_456

I need output as follows,

Name   
-------
Test2 
Test4

I need to do group by chunkwise and select the Name, such that none of the address has prefix as abc.

Output Explanation in detail:

Test1 has addresses as abc_123, abc_456, xyz_123, abc_123 and at least one of the address has prefix as abc. Hence not in output. Test2 is has addresses as xyz_123, xyz_456 and none of them has prefix abc. Hence in output.

x = select distinct(Name) from My_Table
y = select distinct(Name) from My_Table where Address like 'abc%'

Result  = x - y

I was able to achieve result using 2 queries (as shown above) and then using subtract operation in the result set, but can this be achieved in single query?

Note: I'm using JAVA for query MYSQL DB, hence I can subtract result sets. My table size is huge, hence I want to optimize number of queries!

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

One method uses aggregation and is similar to the other answer:

select name
from my_table
group by name
having sum(address like 'abc%') = 0;

The having clause counts the number of matching rows. The = 0 says there are none for a given name.

Upvotes: 2

Related Questions