OraFAQ Forum: SQL & PL/SQL » Produce output not having any or all inputs (products)

Home » SQL & PL/SQL » SQL & PL/SQL » Produce output not having any or all inputs (products)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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: 68637
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.

Report message to a moderator

Previous Topic:	Returning Old value during update
Next Topic:	invalid file operation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 16:42:11 CDT 2024