File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes which join to use? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "which join to use?" Watch "which join to use?" New topic

which join to use?

trupti nigam
Ranch Hand

Joined: Jun 21, 2001
Posts: 614
Hello All,

I have below conditions:
I need to join 3 tables b,c,d where and b.id_nbr = c.id_nbr and and b.id_nbr=d.id_nbr and d.state !=1 and
b.flag = 'some_value' and = ? and c.roll_nbr = ?

the row will always be there in tables b and c ,but it's not necessary that the row will be there in table d. Even in that case the query should retrieve the row from tables b,c and do not apply d.state !=1 condition.

Which type of join needs to be used in above case?

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33117

You can use a subquery that checks for the state being equal to one. Then check that nothing exists in the subquery.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: which join to use?
It's not a secret anymore!