CODEWITHSUNDEEP
sqlt-sql
scripter78
scripter78

Reputation: 1177

SQL Select Where does not include X only

yeah sorry for the piss poor title.

I created an example DB with sqlfiddle but I am so lost on this one I can't even start with the query. http://sqlfiddle.com/#!3/940b7d

in the example DB I need all Account numbers that have equipment and can have equipment "CRAPY" but only if it has any other type of equipment as well. So in the example below I will have a return value of 12345 because that account does have Equipment even though it does also have "CRAPY" it will also return 44444 and 66666 because it has equipment. It will not return 67891 because even though it has equipment it only has "CRAPY" equipment.

God I really hope that is clear, 

create table testdb
(
   Account varchar(5),
   Equipment varchar(5)

)
insert into testdb (Account,Equipment) values ('12345','CDG12')
insert into testdb (Account,Equipment) values ('12345','CRAPY')
insert into testdb (Account,Equipment) values ('12345','CDG12')
insert into testdb (Account,Equipment) values ('12345','CDG12')
insert into testdb (Account,Equipment) values ('12345','CDG12')
insert into testdb (Account,Equipment) values ('67891','CRAPY')
insert into testdb (Account,Equipment) values ('67891','CRAPY')
insert into testdb (Account,Equipment) values ('67891','CRAPY')
insert into testdb (Account,Equipment) values ('67891','CRAPY')
insert into testdb (Account,Equipment) values ('67891','CRAPY')
insert into testdb (Account,Equipment) values ('44444','YYYYY')
insert into testdb (Account,Equipment) values ('66666','PPPPP')

Upvotes: 0

Views: 213

Answers (4)

gadaju
gadaju

Reputation: 416

If we remove the 'CRAPY' records, any accounts left are ones you need to report (have equipment other than 'CRAPY')

select distinct account from testdb where Equipment not like  'CRAPY'

If you need to see what equipment they have, or need to doa quick sanity check:

select account, Equipment
from testdb 
where account in 
   (
    select distinct account from testdb where Equipment not like  'CRAPY'
    )

Upvotes: 0

user5151179
user5151179

Reputation: 585

This should do the trick.

SELECT Account
FROM testdb
WHERE Equipment != 'CRAPY'
GROUP BY Account

You can substitute "<>" where I have "!=" if you prefer that syntax.

Edit: A lot of the other answers in here use DISTINCT. As a DBA, any query that uses DISTINCT will not pass my code review. It is lazy programming, even if it is logically equivalent to using GROUP BY. Please avoid lazy DISTINCT queries.

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

I think you're suffering from a poor problem statement. A clearer statement is Return any account number which has non-"CRAPY" equipment. The existence of "CRAPY" equipment is largely irrelevant.

You can do this with:

SELECT DISTINCT Account FROM testdb WHERE Equipment <> 'CRAPY'

which will ignore "CRAPY" records and return only accounts with non-"CRAPY" equipment (regardless of whether that account has or does not have "CRAPY" equipment -- we don't care about that).

Upvotes: 2

cliffordheath
cliffordheath

Reputation: 2606

Use a DISTINCT query with Equipment <> 'CRAPY':

SELECT DISTINCT Account FROM testdb
WHERE Equipment <> 'CRAPY'

or if you want all the other fields for all records of such accounts, use EXISTS:

SELECT * FROM testdb
WHERE EXISTS(
    SELECT * FROM testdb AS t1
    WHERE testdb.Account = t1.Account
      AND t1.Account <> 'CRAPY'
)

Upvotes: 0

Related Questions