SQL select problem

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
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

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
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
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Thinking about it something like this should work :
Select modules.modules From module where modules.modules <> moduleocc.modules
 
Last edited:

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Well I sorted it out, this is my final statement
VB.NET:
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
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
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

Member
Joined
Jun 9, 2004
Messages
9
Programming Experience
3-5
sql statement

TPM said:
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.
 
Top Bottom