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 |
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 |
|
Michel Cadot
Messages: 68665 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.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:06:49 CDT 2024
|