query look for orders with items having given weight-CodePudding

I have below query:

select a.order_id, b.item_name, b.item_weight from orders a join items b on a.item_id = b.item_id

now my task is this. An order can have multiple items. Now I have to see each item of an order and select orders such that it is not having any item_weight more than 50.

For example, if above query returns:

order_id , item_name, item_weight
1       ,  toy      , 10
1       , plate     , 30
1       , book      , 60
2       , toy       , 20
2       ,book       , 30
2       , plate     , 40
2       ,   apple   , 10

Answer should be to select only order id 2 as it has all items whose weights are less than 50.

CodePudding user response：

A number of ways you can do this, some of which depending on your dbms. This method should work for most:

    select a.order_id,  b.item_name,  b.item_weight 
    from orders a 
    join items b 
      on a.item_id = b.item_id
    where a.order_id not in 
       (select a.order_id 
        from orders a
        join items b
          on a.item_id = b.item_id
        where b.item_weight >= 50
        )