CODEWITHSUNDEEP
sqloracle-databaseselectoracle11g
chalitha geekiyanage
chalitha geekiyanage

Reputation: 6812

Retrieve rows from a table which has multiple dependencies

I'm using Oracle 11g DB and I need to retrieve row id 2 from Table A, as it has two rows on Table B where the Table D's status is true TRUE. Row id 1 from Table A cannot be retrieve, as it has two rows but out-of those two rows one of the row's Table D's status is FALSE.

How to select above rows from a single sql select query?

Table A

Id  | FName      | LName
-----------------------------
1   | Jhone      | A
2   | Alice      | B
3   | Bob        | C

Table B

A.Id  | order_id    
--------------
1     | 1     
1     | 2     
2     | 3
2     | 1

Table C

B.order_id  | order_type    
--------------
1           | X     
2           | Y     
3           | Z

Table D

C.order_type  | order_status    
--------------
X           | TRUE     
Y           | FALSE     
Z           | TRUE

Upvotes: 1

Views: 233

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

This returns all a where there is no false d, which is what I think you want:

select a.*
from a
where not exists (select 1
                  from b join
                       c
                       on b.order_id = c.order_id join
                       d
                       on c.order_type = d.order_type
                  where b.id = a.id and
                        d.order_status = 'false'
                 );

Upvotes: 1

Related Questions