CODEWITHSUNDEEP
sqlsql-server
user2487130
user2487130

Reputation: 29

SQL exclude all rows that with have certain value?

i have a table with these value, and here is a small sample

order   state   item id
10064315    ON  MCM1L162L116
10064315    ON  MCM1R162R116
10064315    ON  SHIPPING
10064316    MS  00801-1778
10064316    MS  SHIPPING
10064317    AZ  CHM110439-1
10064317    AZ  SHIPPING
10064318    TX  2607
10064318    TX  SHIPPING
10064319    MO  CHG8080
10064319    MO  SHIPPING
10064322    CA  W10001130

I want to write a sql query that only list on the order number that without SHIPPING, in this sample, the only one without SHIPPING would be 10064322.

I try to find in here but didn't find what I am looking for.

Thank for the help

Upvotes: 0

Views: 196

Answers (3)

Hogan
Hogan

Reputation: 70528

SELECT DISTINCT [order] 
FROM MYTABLE
WHERE [order] not in (SELECT [order] 
                      FROM MYTABLE 
                      WHERE [item id] = 'SHIPPING')

EDIT:

According to Aaron Bertrand's article (hat tip @TTeeple in the comments) the best performance for this problem (needing the distinct) is done as follows:

SELECT [order]
FROM MYTABLE
  EXCEPT
SELECT [order]
FROM MYTABLE 
WHERE [item id] = 'SHIPPING'

For the full article -> http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Use conditional aggregation:

select [order] from
table
group by [order]
having sum(case when [item id] = 'SHIPPING' then 1 else 0 end) = 0

Upvotes: 2

sgeddes
sgeddes

Reputation: 62861

One option is to use not exists:

select ordernumber
from yourtable y
where not exists (
    select 1
    from yourtable y2
    where y.ordernumber = y2.ordernumber and y2.itemid = 'SHIPPING'
    )

Upvotes: 2

Related Questions