Using a subquery to remove grouped rows with specific value

Question

Please see:

I have these tables:

CREATE TABLE Item 
(
    OrderNo varchar(10),
    ItemNo varchar(10)
);

CREATE TABLE Order1 
(
    OrderNo varchar(10),
    Month varchar(10)
);

INSERT INTO Item (OrderNo, ItemNo)
VALUES ('111','222'), ('111','333'),
       ('333','444'), ('333','222'), ('333','555'),
       ('444','222'), ('444','555');

INSERT INTO Order1 (OrderNo, Month)
VALUES ('111','Feb'), ('333', 'Jan'), ('444', 'May');

I am looking to return orders which don't have the item '555'. I figured this would be a subquery, but I just cant seem to get it right.

I have tried:

SELECT 
    O.[OrderNo],
    O.[Month],
    I.[ItemNo]
FROM 
    Order1 O
JOIN 
    Item I ON O.[OrderNo] = I.[OrderNo]
WHERE 
    O.[OrderNo] IN (SELECT I.[OrderNo]
                    FROM Item I
                    GROUP BY I.[OrderNo], I.[ItemNo]
                    HAVING I.[ItemNo] <> '555')

Which returns this result:

| OrderNo | Month | ItemNo |
|---------|-------|--------|
|     111 |   Feb |    222 |
|     111 |   Feb |    333 |
|     333 |   Jan |    444 |
|     333 |   Jan |    222 |
|     333 |   Jan |    555 |
|     444 |   May |    222 |
|     444 |   May |    555 |

But in this case I want to remove all orders with item '555', so a result that looks like:

| OrderNo | Month | ItemNo |
|---------|-------|--------|
|     111 |   Feb |    222 |
|     111 |   Feb |    333 |

Possible duplicate of [SELECT \* WHERE NOT EXISTS](https://stackoverflow.com/questions/915643/select-where-not-exists) — jose_bacoy, Apr 19 '18 at 11:02

Andrew · Accepted Answer · 2018-04-19T12:33:41.997

1

You just need to reverse your thinking - you want the ones NOT in the list of orders with a 555.

SELECT 
O.[OrderNo],
O.[Month],
I.[ItemNo]
FROM Order1 O
JOIN Item I ON O.[OrderNo] = I.[OrderNo]
WHERE O.[OrderNo] NOT IN
( SELECT I.[OrderNo]
FROM Item I
WHERE I.[ItemNo] = '555')

http://sqlfiddle.com/#!18/479d1/20

edited Apr 19 '18 at 12:33

answered Apr 19 '18 at 10:56

Andrew

26,629
5
63
86

You don't need the grouping in the subquery. – Racil Hilan Apr 19 '18 at 11:09
@RacilHilan - correct, it doesn't need it explicitly – Andrew Apr 19 '18 at 12:32

Racil Hilan · Answer 2 · 2018-04-19T11:04:01.120

1

How about a simple subquery without grouping? Simply change IN to NOT IN, change the condition from <> '555' to = '555', and remove the grouping:

SELECT O.[OrderNo], O.[Month], I.[ItemNo]
FROM Order1 O
INNER JOIN Item I ON O.[OrderNo] = I.[OrderNo]
WHERE O.[OrderNo] NOT IN (SELECT [OrderNo]
                          FROM Item
                          WHERE [ItemNo] = '555')

edited Apr 19 '18 at 11:04

answered Apr 19 '18 at 10:58

Racil Hilan

24,690
13
50
55

score 0 · Answer 3 · answered Apr 19 '18 at 10:57

Just use not exists:

select o.*
from order1 o
where not exists (select 1
                  from items i
                  where i.orderno = o.orderno and i.itemno = 555
                 );

If you want all the items as well as the orders, you can join them in:

select o.*, i.itemno
from order1 o join
     items i
     on o.orderno = i.orderno
where not exists (select 1
                  from items i
                  where i.orderno = o.orderno and i.itemno = 555
                 );

score 0 · Answer 4 · answered Apr 19 '18 at 11:13

0

Try simple subquery.

Select a.OrderNo, a.Month, b.ItemNo 
From Order1 a  
Inner Join  Item b 
On b.OrderNo = a.OrderNo 
Where a.OrderNo  
Not In (Select OrderNo From Item Where Itemno = '555')

answered Apr 19 '18 at 11:13

Pravin .Kadam

99
1
7

My sincere apology, I was working on query in my SSMS on my machine. Did not refresh the page before posting the answer. did not realize you already posted same. Will delete it once you acknowledge. – Pravin .Kadam Apr 19 '18 at 13:15

Using a subquery to remove grouped rows with specific value

4 Answers4