Re: SQL Query

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1997/12/05
Message-ID: <6695hb$8a2$1@news02.btx.dtag.de>#1/1

Niloufar Lamei wrote:
>
> 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.

Hi,

look what I did and check if it's what you want:

drop table tableA;

create table tableA
(
id number,
filename varchar2(25),
filesize varchar2(20)
);

drop table tableB;

create table tableB
(
fname varchar2(25),
fsize varchar2(20)
);

insert into  tableA                
Values (1 ,  'app1' ,        '1111'          );
insert into  tableA                
Values (2,   'app2'  ,       '2222'         );       
insert into  tableA                
Values (2 ,  'app2'  ,       '6666'          );
insert into  tableA                
Values (3 ,  'extra' ,       '5555'         );
insert into  tableA                
Values (3 ,  'extra' ,       '7777'         );


insert into  tableB                
Values ('app1',   '1111');
insert into  tableB                

Values ('app2', '2222');

SELECT DISTINCT
        *
FROM
        tableA A
WHERE

	NOT EXISTS
	(SELECT 
		*
	FROM
		tableB
	WHERE
		tableB.fname=A.filename
		AND
		tableB.fsize=A.filesize
	)

;                  

Returns:

       ID FILENAME                  FILESIZE
--------- ------------------------- --------------------
        2 app2                      6666
        3 extra                     5555
        3 extra                     7777

-- 
Regards

Matthias Gresz    :-)

Received on Fri Dec 05 1997 - 00:00:00 CST