Multi not like expression sql

Question

So basically i've a table like this.

+--------+----------+   
|   name |  Group   |  
+--------+----------+   
|   xxxx |   1      |  
|   yyyy |   1      |  
|   xxxx |   2      |  
|   yyyy |   3      |  
|   xxxx |   4      |  
+--------+----------+

and i don't want to display any records that have name xxxx in their group.

So you only want to show the row in Group 3? because grps 1,2 and 4 all contains something with xxxx as a name? — Nick is tired, Dec 04 '18 at 09:54

score 1 · Accepted Answer · answered Dec 04 '18 at 09:53

1

You seem want :

select t.*
from table t
where not exists (select 1 from table t1 where t1.group = t.group and t1.name = 'xxxx')

answered Dec 04 '18 at 09:53

Yogesh Sharma

49,870
5
26
52

your awswer seem to be right but i got some problem with it cause im using CTE and my query after CTE implementation looks like this SELECT DISTINCT c.TWG_Kod as NAME, g1.TwG_Kod as GROUP, k.twr_nazwa, m.MAG_Nazwa, m.MAG_Kod FROM CTE c join cdn.TwrGrupy g1 with (nolock) on c.TwG_GIDNumer = g1.TwG_GrONumer. And i don't know how to manage to use it in your querry cause of column name duplication – Zackk Dec 04 '18 at 11:32

score -1 · Answer 2 · answered Dec 04 '18 at 09:54

-1

You need a subquery at first to identify groups you do not need. then you need to filter it from main query

SELECT * FROM Table1 
Where group not in ( select group from Table1 where name != 'xxxx')

answered Dec 04 '18 at 09:54

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

I would avoid using `IN()` while I can use `EXISTS()` – Ilyes Dec 04 '18 at 09:56
why? they both have usage areas. I think this is very wide topic to be discussed here – Derviş Kayımbaşıoğlu Dec 04 '18 at 09:58
1

Why? ok [see why here](https://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance). – Ilyes Dec 04 '18 at 10:00
1

@Sami *"This used to be true but in current versions (at least 2008) the optimizer is much smarter... it actually treats IN () just like an EXISTS ()"*... – Nick is tired Dec 04 '18 at 10:05
@NickA Well, the OP did not specify the version yet :) – Ilyes Dec 04 '18 at 10:09
but the answer is totally correct and it does not deserve to be downvoted. – Derviş Kayımbaşıoğlu Dec 04 '18 at 10:10
@Sami I like to think not pre-2008 :) – Nick is tired Dec 04 '18 at 10:12
Many find the `NOT IN` result when there are null values returned from the sub-query a bit surprising. – jarlh Dec 04 '18 at 10:21

Multi not like expression sql

2 Answers2