CODEWITHSUNDEEP
sqlsql-server-2008-r2
user867621
user867621

Reputation: 1197

How to find all items in table that don't have a certain text in column

I have a table iminvbin_sql.. This table has columns item_no, loc, bin_no. Each item number should have 4 bins in location 2. how do I find all the items that do not have these four bins in loc 2?

I tried 

select item_no from iminvbin_sql where bin_no not in('910SHIP','910STAGE','910PROD','1') AND loc = 2

but that didn't work.

itemno loc  bin

0       2    1 
0       2    910PROD
0       2    910SHIP
0       2    910STAGE

Upvotes: 0

Views: 55

Answers (3)

Bohemian
Bohemian

Reputation: 425128

Use a group by with a having clause to determine if all bins are not there:

select item_no
from iminvbin_sql
group by item_no
having sum(case when loc = 2 and bin_no in ('910SHIP','901STAGE','910PROD','1') then 1 end) < 4

The important point with this query is that by moving the condtions into the case, it will find items that have none any of the listed bins or even items that have no data for loc = 2.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270201

I think this is what you want:

select item_no
from iminvbin_sql
where bin_no in ('910SHIP', '901STAGE', '910PROD', '1') and
      loc = 2
group by item_no
having count(distinct bin_no) <> 4;

This will check that all four of those values are in the bins. If you want to verify that these four values are in the bins and no other values are, you can test for this as well:

select item_no
from iminvbin_sql
where loc = 2
group by item_no
having count(distinct bin_no) <> 4 or
       count(distinct case when bin_no in ('910SHIP', '901STAGE', '910PROD', '1') then bin_no end) <> 4 or
       count(*) <> 4;

EDIT:

In response to Bohemian's comment, the following should get all items that are not fully populated:

select item_no
from iminvbin_sql
group by item_no
having count(distinct case when loc = 2 then bin_no end) <> 4 or
       count(distinct case when loc = 2 then bin_no in ('910SHIP', '901STAGE', '910PROD', '1') then bin_no end) <> 4 or
       sum(case when loc = 2 then 1 else 0 end) <> 4;

Upvotes: 1

MatBailie
MatBailie

Reputation: 86745

SELECT
  itemno
FROM
  iminvbin_sql
WHERE
  loc = 2
GROUP BY
  itemno
HAVING
  4 <> SUM(CASE WHEN bin IN ('910SHIP','901STAGE','910PROD','1') THEN 1 ELSE 0 END)

Upvotes: 0

Related Questions