Home » SQL & PL/SQL » SQL & PL/SQL » Produce output not having any or all inputs (products)
Produce output not having any or all inputs (products) [message #662687] Tue, 09 May 2017 10:09 Go to next message
ganesh_jadhav0509
Messages: 74
Registered: May 2007
Location: Chester
Member

Hi Guys

I have two tables structures and inserts as follows


[u]Products[/u]
productno	Products 	State
1001	a	liquid
1002	b	liquid
1003	c	liquid
1004	d	Solid
1005	e	Solid
1006	f	Solid

[u]Orders[/u]
orderno	Productno
101	1001
101	1003
101	1002
102	1001
102	1004
102	1005
102	1006
103	1006
104	1002
105	1004
106	1001
106	1002
106	1003
106	1006


And insert statements
CREATE TABLE product (productno NUMBER,product VARCHAR2(10),states VARCHAR2(15));
INSERT INTO product (productno,product,states) VALUES (1001,'a','liquid');
INSERT INTO product (productno,product,states) VALUES (1002,'b','liquid');
INSERT INTO product (productno,product,states) VALUES (1003,'c','liquid');
INSERT INTO product (productno,product,states) VALUES (1004,'d','Solid');
INSERT INTO product (productno,product,states) VALUES (1005,'e','Solid');
INSERT INTO product (productno,product,states) VALUES (1006,'f','Solid');

CREATE TABLE orders (ordersno NUMBER,productno NUMBER);
INSERT INTO orders (ordersno,productno) VALUES (101,1001);
INSERT INTO orders (ordersno,productno) VALUES (101,1003);
INSERT INTO orders (ordersno,productno) VALUES (101,1002);
INSERT INTO orders (ordersno,productno) VALUES (102,1001);
INSERT INTO orders (ordersno,productno) VALUES (102,1004);
INSERT INTO orders (ordersno,productno) VALUES (102,1005);
INSERT INTO orders (ordersno,productno) VALUES (102,1006);
INSERT INTO orders (ordersno,productno) VALUES (103,1006);
INSERT INTO orders (ordersno,productno) VALUES (104,1002);
INSERT INTO orders (ordersno,productno) VALUES (105,1004);
INSERT INTO orders (ordersno,productno) VALUES (106,1001);
INSERT INTO orders (ordersno,productno) VALUES (106,1002);
INSERT INTO orders (ordersno,productno) VALUES (106,1003);
INSERT INTO orders (ordersno,productno) VALUES (106,1006);


I want to build a query to list down orders which does not have all Liquid products.

For Example
1) orderno 101 & 106 should not be in output as all 3 liquid products(1001,1002,1003) are there.
2) orderno 102 Should be in output as only one liquid products (1001) is there. Same with orderno 104
3) orderno 103 & 105 Should be in output as non of liquid products in there.

[Updated on: Tue, 09 May 2017 10:10]

Report message to a moderator

Re: Produce output not having any or all inputs (products) [message #662691 is a reply to message #662687] Tue, 09 May 2017 11:06 Go to previous message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with liquids as (select count(*) liquid_cnt from product where states = 'liquid')
  2  select ordersno
  3  from orders o join product p on p.productno = o.productno
  4  group by ordersno
  5  having count(decode(states, 'liquid', states)) != (select liquid_cnt from liquids)
  6  order by 1
  7  /
  ORDERSNO
----------
       102
       103
       104
       105

4 rows selected.
Previous Topic: Returning Old value during update
Next Topic: invalid file operation
Goto Forum:
  


Current Time: Wed Oct 17 18:26:27 CDT 2018