Find elements from table 1 that don't exist in table 2

[goldenLiv]

Hi,

I am a software programmer and not a pl/sql programming.
Hope this is the right place to post my question.

It is about comparing two tables.

I would like to find the elements from table1 that do not exist in table2

This is my query (which is not functional)
SELECT *
FROM TABLE_1
WHERE EXISTS
(SELECT *
FROM TABLE_2
WHERE TABLE_2.MODIFDATE IS NULL);

and the tables

create table TABLE_1
(
NAME VARCHAR2(50),
SURNAME VARCHAR2(50),
MODIFDATE DATE
)

create table TABLE_2
(
NAME VARCHAR2(50),
SURNAME VARCHAR2(50),
MODIFDATE DATE
)

Thanks.

Liv

 

[Scunningham99]

select name, surname, modifdate
from table_1
minus
select name, surname, modifdate
from table_2

should work


Sy UK

 

[goldenLiv]

Sorry,

I should have said that I don't want to list the parameters as for my project, those parameters would be unknown to me.

I am looking for something that is generic.

Thanks

 

[Turkbear]

Hi,
Please explain wht you mean by:

I don't want to list the parameters as for my project, those parameters would be unknown

What parameters?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[goldenLiv]

Hi

I would to make a select * and select name, surname, etc...

These are my tables

TABLE1
NAME SURNAME MODIFDATE
1 Williams1 Bernie 2006-10-30 14:25:15
2 Williams2 Bernie 2006-10-30 14:25:15
3 Williams3 Bernie 2006-10-30 14:25:15
4 Williams4 Bernie 2006-10-30 14:25:15
5 Williams5 Bernie 2006-10-30 14:25:15
6 Williams6 Bernie 2006-11-02 10:27:10
7 Williams7 Bernie 2006-10-30 14:25:15
8 Williams8 Bernie 2006-10-18 11:07:06
9 Williams9 Bernie 2006-10-30 14:25:15
10 Williams10 Bernie 2006-10-30 14:25:15

TABLE2
NAME SURNAME MODIFDATE
1 Williams1 Bernie 2006-10-30 14:25:15
2 Williams2 Bernie 2006-10-30 14:25:15
3 Williams3 Bernie 2006-10-30 14:25:22
4 Williams6 Bernie 2006-10-30 14:25:22
5 Williams7 Bernie 2006-10-30 14:25:22
6 Williams8 Bernie 2006-10-30 14:25:22
7 Williams9 Bernie 2006-10-30 14:25:22
8 Williams10 Bernie 2006-10-30 14:25:22

I would like to make query so as to get these two records from table1:
4 Williams4 Bernie 2006-10-30 14:25:15
5 Williams5 Bernie 2006-10-30 14:25:15

The select minus does not give the result I am looking for

thanks

 

[Turkbear]

Hi,
So you tried:

Select name,surname,modifdate from table1
minus
Select name,surname,modifdate from table2

and did not get those 2 records?

Part of the reason is that
Williams3 Bernie 2006-10-30 14:25:15 ( Table1)
is NOT the same as
Williams3 Bernie 2006-10-30 14:25:22 (table2)

Try just

Select name,surname from table1
minus
Select name,surname from table2

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[goldenLiv]

Well this work.

I guess I have to forget not mentioning the columns of the table.

Thanks.