×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

UNION in NOT IN

UNION in NOT IN

UNION in NOT IN

(OP)
Hi,

I have an Interbase 6 database with around 200 tables, some of which have an associated audit table named AUDIT_tablename (e.g. AGENT and AUDIT_AGENT).  I am trying to find all tables in the database which do not already have an audit table, and I'm finding it a bit tricky.  As far as I know the only place to find tables is in the RDB$RELATIONS system table, but this also holds views, so my select statement needs to be along the lines of:

select rdb$relation_name
from rdb$relations
where rdb$system_flag = 0
and rdb$relation_name not in
(select distinct rdb$view_name from rdb$view_relations
UNION
select r1.rdb$relation_name
from rdb$relations r1, rdb$relations r2
where r1.rdb$relation_name = 'AUDIT_'||r2.rdb$relation_name)

but this gives me an error (code 104) on the 'UNION'.  Does anyone know why?
I can get around it by doing this:

select rdb$relation_name
from rdb$relations r1
where rdb$system_flag = 0
and rdb$relation_name not in
(select distinct rdb$view_name from rdb$view_relations)
and rdb$relation_name not in
(select r1.rdb$relation_name
from rdb$relations r1, rdb$relations r2
where r1.rdb$relation_name = 'AUDIT_'||r2.rdb$relation_name)

but this strikes me as being a very inefficient select statement, does anyone know a better way of achieving what I want to do?

Thanks

Steve

RE: UNION in NOT IN

This should help, the following query will list VIEWS only - I guess you can change it to get tables only

select * from rdb$relations where rdb$view_blr is not null

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

RE: UNION in NOT IN

(OP)
Thanks Martijn, I hadn't spotted the rdb$view_blr column.
There was also a mistake in my sql (it was excluding the audit_ table, but not the tables being audited).  My final sql is thus:

select rdb$relation_name table_name
from rdb$relations
where rdb$system_flag = 0
and rdb$view_blr is null
and rdb$relation_name not like 'AUDIT_%'
and rdb$relation_name not in
  (select r2.rdb$relation_name
   from rdb$relations r1, rdb$relations r2
   where r1.rdb$relation_name = 'AUDIT_'||r2.rdb$relation_name)
order by rdb$relation_name

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close