Home » SQL & PL/SQL » SQL & PL/SQL » select where is null
select where is null [message #270218] Wed, 26 September 2007 03:21 Go to next message
hermiod
Messages: 7
Registered: September 2007
Location: UK
Junior Member
Hi guys.

I have one table (stmbiogr) which contains a record for each student. It has a student_id field.

There is another table called stmaos. What I want to do is check if there is a corresponding student_id in stmaos for the student_id in stmbiogr. If not, return the stmbiogr.student_id.

I think this will require a sub-query but I have only used these once so I'm pretty much shooting in the dark here. This is what I've got so far:
select distinct stmbiogr.student_id
from stmaos, stmbiogr
where substr(stmbiogr.student_id,4,2) = '06'
not in count 
(select stmaos.student_id
from stmaos
where count(stmaos.student_id = 0)
group by stmaos.student_id)


The way I think it needs to be done is by counting the records in stmaos against a record in stmbiogr. If the count in stmaos is 0 then it will return the stmbiogr.student_id.
Not sure if I'm on the right path with this.

And I believe we are using oracle 9.2.1.0.
Re: select where is null [message #270220 is a reply to message #270218] Wed, 26 September 2007 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NOT EXISTS

Regards
Michel
Re: select where is null [message #270235 is a reply to message #270220] Wed, 26 September 2007 04:21 Go to previous message
hermiod
Messages: 7
Registered: September 2007
Location: UK
Junior Member
That's great Michel, thanks alot. Did the trick perfectly.

[Updated on: Wed, 26 September 2007 04:21]

Report message to a moderator

Previous Topic: pair date range from 2 tabs
Next Topic: Count distinct values in column for >2 tables
Goto Forum:
  


Current Time: Thu Dec 08 08:16:18 CST 2016

Total time taken to generate the page: 0.19517 seconds