Not getting the right table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not getting the right table

Author

Topic

rluxmi
Starting Member

6 Posts

Posted - 2009-05-14 : 17:39:42

Code

INSERT INTO WorksOn VALUES ('Smith','X');
INSERT INTO WorksOn VALUES ('Smith','Y');
INSERT INTO WorksOn VALUES ('Doe','Y');
INSERT INTO WorksOn VALUES ('Brown','X');
INSERT INTO WorksOn VALUES ('Doe','Z');
INSERT INTO WorksOn VALUES ('Chang','X');
INSERT INTO WorksOn VALUES ('Brown','Y');
INSERT INTO WorksOn VALUES ('Brown','Z');

INSERT INTO SmithProject VALUES ('X');
INSERT INTO SmithProject VALUES ('Y');

DROP TABLE WorksOn;
CREATE TABLE WorksOn
(
name VARCHAR2(32),
project VARCHAR2(16),
hours NUMBER
);

DROP TABLE SmithProject;
CREATE TABLE SmithProject
(
project VARCHAR2(16)
);

INSERT INTO WorksOn VALUES ('Smith','X',10);
INSERT INTO WorksOn VALUES ('Smith','Y',20);
INSERT INTO WorksOn VALUES ('Doe','Y',20);
INSERT INTO WorksOn VALUES ('Brown','X',10);
INSERT INTO WorksOn VALUES ('Doe','Z',30);
INSERT INTO WorksOn VALUES ('Chang','X',10);
INSERT INTO WorksOn VALUES ('Brown','Y',20);
INSERT INTO WorksOn VALUES ('Brown','Z',10);

SELECT name,project,hours FROM workson w1
WHERE NOT EXISTS (

(SELECT project FROM workson w2 WHERE w2.name = w1.name and w2.hours = w1.hours
MINUS
SELECT project FROM workson WHERE name = 'Smith' )

UNION

(SELECT project FROM workson w2 WHERE name = 'Smith'
MINUS
SELECT project FROM workson w2 WHERE w2.name = w1.name )

)

Result I want is

Smith x 10
Smith y 20
Brown x 10
Brown y 20

The result I am getting right now is
Smith x 10
Smith y 20
Brown y 20

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 22:50:03

Are you using Oracle ?

This is a Microsoft SQL Server forum. Please post your question on Oracle over at dbforums.com or orafaq.com

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources