CODEWITHSUNDEEP
sql-server
Marcelo
Marcelo

Reputation: 47

How to apply a filter to a group of records in SQL SERVER

I have a list of clients with purchases in different category lines. I must query and get back those who have bought a particular line.

For instance:

RUT_DV_LOCAL    NAME        DESC_LINEA
10006919_9_1    CURIQUEO        TRADICIONAL
10006919_9_1    CURIQUEO        REFRIGERADOS
10015983_K_3    NAVARRETE       CONFITES
10015983_K_3    NAVARRETE       TRADICIONAL
10023883_7_3    EZPINOZA        CONFITES
10028999_7_1    ASTUDILLO       CONFITES
10028999_7_1    ASTUDILLO       REFRIGERADOS
10028999_7_1    ASTUDILLO       TRADICIONAL

The query I am looking for should give back those clients who have not bought 'CONFITES', having the following as a result:

RUT_DV_LOCAL    NAME        DESC_LINEA
10006919_9_1    CURIQUEO        TRADICIONAL
10006919_9_1    CURIQUEO        REFRIGERADOS

Or at least just the client's name.

It must be a query and not an SP due to how it will be used down the road.

Thanks.

Upvotes: 2

Views: 40

Answers (1)

SqlZim
SqlZim

Reputation: 38033

using not exists():

select *
from t
where not exists (
  select 1
  from t as i
  where i.rut_dv_local = t.rut_dv_local
    and i.desc_line = 'CONFITES'
  )

Using not exists() with your joins:

select 
    rut_dv_local
  , nombre
  , desc_linea
from clientes c
  inner join linea l
    on c.id_cliente = l.id_cliente
where not exists (
  select 1
  from linea il 
  where il.id_cliente = c.id_cliente
    and il.desc_line = 'CONFITES'
  )

Upvotes: 3

Related Questions