Output mystery??

[adarshakb]

select order# from order_item o
where not exists
((select warehouse# from warehouse where city='Bidar')
minus
(select warehouse# from shippment s where s.order#=o.order#));

select * from order_item;
SELECT * FROM warehouse;
SELECT * FROM shippment;


ORDER#
5

ORDER# ITEM# QTY
1...........2...........5
1...........5...........4
2...........5...........3
3...........1...........4
4...........2...........8
5...........3...........2

6 rows selected.

WAREHOUSE# CITY
1......................Bidar
2......................Bagalkot
3 ......................Bijapur
4 ......................Bidar
5 ......................Bannerghatta
6 ......................Bidar

6 rows selected.

ORDER# WAREHOUSE# SHIP_DATE
1......................2 ...........02-JAN-90
1......................4 ...........02-JAN-90
2......................1...........03-JAN-90
3......................5...........04-JAN-90
4 ......................2...........05-JAN-90
5......................4...........06-JAN-90
5 ......................1...........06-JAN-90
5......................6...........06-JAN-90


-----------------------------------------------------------------

My question is:

Why/How 5 come in the 1st table as answer
(select warehouse# from warehouse where city='Bidar')
minus
(select warehouse# from shippment s where s.order#=o.order#)

return NULL

But still we get output as 5

and not as 1 2 3 4 5

[guelphdad]

What database are you using? Your question doesn't appear to be about MySQL. I can move the question to the general database forum, but want to specify the database application in the thread title as required.

[adarshakb]

What database are you using? Your question doesn't appear to be about MySQL. I can move the question to the general database forum, but want to specify the database application in the thread title as required.

Oracle 9i

Here is the SQL code for any one who likes to solve the question posed as to Retrive the order number which is in a specific city's ALL warehouse

i.e. if warehouse 1,4,6 are in A and order X is located in 1,4 and order Y in 1,4,6 then the answer is Y

CREATE TABLE customer (
cust# NUMBER(10) CONSTRAINT cc1 PRIMARY KEY,
cname VARCHAR(20) CONSTRAINT cc2 NOT NULL,
city VARCHAR(30)
);

CREATE TABLE ord (
order# NUMBER(10) CONSTRAINT o1 PRIMARY KEY,
odate DATE CONSTRAINT o2 NOT NULL,
cust# NUMBER(10) CONSTRAINT o3 REFERENCES customer(cust#),
ord_amt NUMBER(10,3) CONSTRAINT o4 CHECK(ord_amt>0)
);

CREATE TABLE item (
item# NUMBER(10) CONSTRAINT i1 PRIMARY KEY,
unitPrice NUMBER(10,3) CONSTRAINT i2 CHECK(unitPrice>0)
);

CREATE TABLE order_item (
order# NUMBER(10) CONSTRAINT oi1 REFERENCES ord(order#) ON DELETE CASCADE,
item# NUMBER(10) CONSTRAINT oi2 REFERENCES item(item#) ON DELETE CASCADE,
qty NUMBER(6) CONSTRAINT oi3 CHECK(qty>0),
CONSTRAINT oi4 PRIMARY KEY(order#,item#)
);

CREATE TABLE warehouse (
warehouse# NUMBER(10) CONSTRAINT w1 PRIMARY KEY,
city VARCHAR(30)
);

CREATE TABLE shippment (
ORDER# NUMBER(10) CONSTRAINT s1 REFERENCES ord(order#) ON DELETE CASCADE,
warehouse# NUMBER(10) CONSTRAINT s2 REFERENCES warehouse(warehouse#) ON DELETE CASCADE,
ship_date DATE CONSTRAINT s3 NOT NULL,
CONSTRAINT s4 PRIMARY KEY(warehouse#,order#)
);

commit;

INSERT INTO customer VALUES(1,'Adarsha','Bangalore');
INSERT INTO customer VALUES(2,'Ashish','Bangalore');
INSERT INTO customer VALUES(3,'Bhavesh','Bangalore');
INSERT INTO customer VALUES(4,'Anirudh','Bangalore');
INSERT INTO customer VALUES(5,'Abhishek','Bangalore');


INSERT INTO ord VALUES(1,'1-Jan-1990',1,2500);
INSERT INTO ord VALUES(2,'2-Jan-1990',1,4500);
INSERT INTO ord VALUES(3,'3-Jan-1990',2,6955);
INSERT INTO ord VALUES(4,'4-Jan-1990',4,1778);
INSERT INTO ord VALUES(5,'5-Jan-1990',5,2345);


INSERT INTO item VALUES(1,89);
INSERT INTO item VALUES(2,87);
INSERT INTO item VALUES(3,45);
INSERT INTO item VALUES(4,47);
INSERT INTO item VALUES(5,56);


INSERT INTO order_item VALUES(1,2,5);
INSERT INTO order_item VALUES(1,5,4);
INSERT INTO order_item VALUES(2,5,3);
INSERT INTO order_item VALUES(3,1,4);
INSERT INTO order_item VALUES(4,2,8);
INSERT INTO order_item VALUES(5,3,2);

INSERT INTO warehouse VALUES(1,'Bidar');
INSERT INTO warehouse VALUES(2,'Bagalkot');
INSERT INTO warehouse VALUES(3,'Bijapur');
INSERT INTO warehouse VALUES(4,'Bidar');
INSERT INTO warehouse VALUES(5,'Bannerghatta');
INSERT INTO warehouse VALUES(6,'Bidar');

INSERT INTO shippment VALUES(1,2,'2-Jan-1990');
INSERT INTO shippment VALUES(1,4,'2-Jan-1990');
INSERT INTO shippment VALUES(2,1,'3-Jan-1990');
INSERT INTO shippment VALUES(3,5,'4-Jan-1990');
INSERT INTO shippment VALUES(4,2,'5-Jan-1990');
INSERT INTO shippment VALUES(5,4,'6-Jan-1990');
INSERT INTO shippment VALUES(5,1,'6-Jan-1990');
INSERT INTO shippment VALUES(5,6,'6-Jan-1990');

SELECT * FROM customer;
SELECT * FROM ord;
SELECT * FROM item;
SELECT * FROM order_item;
SELECT * FROM warehouse;
SELECT * FROM shippment;

[guelphdad]

In future post questions about non-mysql databases in the general database forums, and mark it similar to

Oracle: subjectname

there are various features that work differently between the databases.

[adarshakb]

any one has any answers to this???:confused:

I am confused