oracle select if there is no entry in second table [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably an开发者_运维百科swered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

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.

---

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.

---

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)

---

I started with Bharat's answer and made some changes:

  select * from table1
    where t1.id in
       (  
        select t1.id from table1 t1, table2 t2  
          where t1.id = t2.id (+) and t2.is is null 
        union all
        select t1.id from table1 t1, table2 t2  
          where t1.id = t2.id and t2.value >  0 
       )

If you prefer ANSI syntax:

  select * from table1 t1
    where t1.id in
       (  
        select t1.id 
           from table1 t1 left outer join table2 t2  
           on t1.id = t2.id 
           where t2.id is null
        union all
        select t1.id 
           from table1 t1 join table2 t2  
           on t1.id = t2.id and t2.value > 0
       )

The way I understand the problem is you want all rows in t1 (the parent) that have no children. That's the select just before the union all. You also want all rows in t1 that have at least one child with value > 0. That's the select after the union all.

Each row in t1 will be selected once, or not at all. Is that what you wanted?

---

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.