Home » SQL & PL/SQL » SQL & PL/SQL » Extracting User who does not exist in column (oracle XE sqlplus 11g)
Extracting User who does not exist in column [message #569635] Tue, 30 October 2012 09:50 Go to next message
b1234b
Messages: 3
Registered: October 2012
Location: sg
Junior Member
I have two tables.

First one is

STAFF
======
STAFFNUM NAME
======== ====
1 A
2 B
3 C

SUBJECT
=======
SUBCODE SUBNAME LECTURER
======= ======= ========
A1 ABC 1
A2 EFG 2
A3 HIJ 1

I did the following query
SELECT STAFF.STAFFNUM, STAFF.NAME, SUBJECT.LECTURER
FROM STAFF,SUBJECT
WHERE STAFF.STAFFNUM NOT IN SUBJECT.LECTURER

It will show me..
B and C is not teaching A1.
A and C is not Teaching A2.
B and C is not teaching A3.

This is not what I want.

What I want is to show who is not teaching any subjects.
So the expected result is only C coming out.
Any idea what query do I need for this?
Re: Extracting User who does not exist in column [message #569636 is a reply to message #569635] Tue, 30 October 2012 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

use MINUS
Re: Extracting User who does not exist in column [message #569638 is a reply to message #569636] Tue, 30 October 2012 10:06 Go to previous messageGo to next message
b1234b
Messages: 3
Registered: October 2012
Location: sg
Junior Member
I am sorry, where do I use the MINUS at?
Re: Extracting User who does not exist in column [message #569640 is a reply to message #569638] Tue, 30 October 2012 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>I am sorry, where do I use the MINUS at?
between STAFF & SUBJECT
Re: Extracting User who does not exist in column [message #569642 is a reply to message #569640] Tue, 30 October 2012 10:53 Go to previous messageGo to next message
b1234b
Messages: 3
Registered: October 2012
Location: sg
Junior Member
Is it

SELECT Staff.Staffnum, Staff.name, Subject.lecturer
FROM Staff, Subject
MINUS
SELECT *
FROM Staff, Subject
WHERE Staff.Staffnum in Subject.lecturer


?
Re: Extracting User who does not exist in column [message #569644 is a reply to message #569642] Tue, 30 October 2012 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
SELECT * FROM STAFF WHERE STAFFNUM IN (SELECT STAFFNUM FROM STAFF MINUS SELECT LECTURER FROM SUBJECT);
Re: Extracting User who does not exist in column [message #569645 is a reply to message #569635] Tue, 30 October 2012 11:38 Go to previous messageGo to next message
tigsav
Messages: 44
Registered: April 2012
Member
Hi Blackswan,

Just a query Can this not be done using
SELECT * FROM STAFF WHERE STAFFNUM NOT IN (SELECT DISTINCT LECTURER FROM SUBJECT);

Any specific reason so as to why you have suggested using MINUS .

Regards,
Tigsav
Re: Extracting User who does not exist in column [message #569646 is a reply to message #569642] Tue, 30 October 2012 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Surely not as it is not a valid syntax.
Maybe you should take your course book and review it.

Regards
Michel
Re: Extracting User who does not exist in column [message #569724 is a reply to message #569635] Wed, 31 October 2012 13:01 Go to previous messageGo to next message
tigsav
Messages: 44
Registered: April 2012
Member
I tried the very same syntax and it worked ,if you can point out the syntax error it would be helpful to the so called reviewing my course book

Thanks
tigsav
Re: Extracting User who does not exist in column [message #569726 is a reply to message #569724] Wed, 31 October 2012 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My answer was for b1234b see the post header.

Regards
Michel
Re: Extracting User who does not exist in column [message #569730 is a reply to message #569635] Wed, 31 October 2012 13:17 Go to previous message
tigsav
Messages: 44
Registered: April 2012
Member
Ahh!! Sorry My Bad Sad

Regards,
tigsav
Previous Topic: Heterogeneous database integrated
Next Topic: A better way to check every table for data?
Goto Forum:
  


Current Time: Fri Dec 19 03:46:58 CST 2014

Total time taken to generate the page: 0.10893 seconds