oracle select if there is no entry in second table

Question

I've two tables where I like to select from the second table where it has a foreign key to first table.

The conditions are: (1). The qty field of second table must have a value greater than 0

Or

(2). First table record doesn't have a corresponding entry in the second table.

Answer 1

I guess you need something like following

select * from the table1 t1
               where t1.value > 0 and 
                     t1.id not in (select distinct foreign_id  from table2 t2)

Answer 2

Maybe this :

select * 
from table1 t1
where t1.qty > 0 
and not exists
(
    select * 
    from table2 t2 
    where t1.id = t2.id
)

Answer 3

Maybe you want this?

select t1.pk, t2.qty
from   t1
left outer join t2 on t2.fk = t1.pk
where (t2.fk is null or t2.qty > 0);

"t2.fk is null" takes care of the t1 rows that have no matching t2 row, and "t2.qty > 0" takes care of the t1 rows that do have a matching t2 row.

Answer 4

If I understand the question correctly I think the following will give you the answer you want:

SELECT *
  FROM TABLE1 t1
  LEFT OUTER JOIN TABLE2 t2
    ON (t2.ID = t1.ID)
  WHERE t2.ID IS NULL OR
        t2.QTY > 0

Share and enjoy.