SQL Query

I have the following problem, could someone help me and tell me if I can do this with SQL.

Thanks,
Niloufar

Problem:
Assume two tables:

tableA (id 	  number,               tableB (name varchar2,
        file_name varchar2,                     size varchar2)
        file_size varchar2) 

tableB is my definition table where I have the list of the names of application files and their sizes and tableA is the list of the files that are availabe on machines with ids 1, 2, ...
The goal is to find those files that exist in tableA but do not have a match in tableB.

example:

         tableA                            tableB
id file_name   file_size                 name   size
1   app1         1111                    app1   1111
2   app2         2222                    app2   2222
2   app2         6666
3   extra        5555
3   extra        7777

So, the query should return:

id file_name file_size

2   app2         6666
3   extra        5555
3   extra        7777

Now, if I just want the file_name, file_size I can get this result using:

(select file_name, file_size from tableA)
minus
(select name, size from tableB)

It still easy to write a query that will just return the two rows that do not exist in the query (e.g., extra) with their respective machine id, but I have not been able to figure out the query that could give me all three rows!

Any help will be appreciated. Thx. Received on Thu Dec 04 1997 - 00:00:00 CST