SQL select problem

[levyuk]

Hi,

I have a slight problem with some sql. I have 2 tables, module and moduleocc. I need to produce a list of modules that do not appear in the moduleocc table. Does anyone know how to do this? I know how to join and use sub queries but i don't know how to check if something doesn't exist in another table.

Cheers

 

[TPM]

Hmm, can't think of a way to do that through an sql select statment (although I haven't finished my morning coffee yet ). What you could do is have 2 selects (one for each table) then compare them with VB.

TPM

 

[levyuk]

It's not in vb, this is just the only place I could think to ask for help.

 

[TPM]

D'oh. You could probably do it with a stored procedure then.

 

[TPM]

Thinking about it something like this should work :
Select modules.modules From module where modules.modules <> moduleocc.modules

 

[levyuk]

Well I sorted it out, this is my final statement

column "Module Code" format a15
select modulecode "Module Code", moduledescription "Description", creditvalue "Credits", awardlevel "Award Level"
from module m
where not exists (select modulecode
				from moduleocc mo
				where m.modulecode = mo.modulecode);

It displays modules which are not in the module tale

 

[TPM]

Out of interest is this an sql script your writing?

TPM

 

[levyuk]

Yes it is a script, just didn't know where to ask for help so I tried here, since there are some intelligent people around here. It's for coursework, I have to make quite a lot of scripts tbh in Oracle

 

[bilro]

sql statement

Thinking about it something like this should work :
Select modules.modules From module where modules.modules <> moduleocc.modules

is you want to compare 2 tables (modules, mosduleocc) you have to compare their fields (they must have the same type)

tables : modules, moduleocc
fields : module

sql : SELECT modules.modules FROM module, moduleocc.modules WHERE modules.modules<>moduleocc.modules.