File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL Query Suggestions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Query Suggestions" Watch "SQL Query Suggestions" New topic
Author

SQL Query Suggestions

Rachel Kumar
Ranch Hand

Joined: Oct 21, 2008
Posts: 51
I have 3 tables as shown below.

i want to retrieve all the 'Rest' that has 'Ant_id = 1 and 2 and 3 and 4 so on'...

i tried the below queries...din work

suggestions please!

Table Rest
----------
Rest_id
Rest

Table Ant
---------
Ant_id
Ant_name

Table RestAnt
-------------
Rest_id
Ant_id


1.select rest from rest where rest_id in ( select rest_id from restant where ant_id in ( 1,2,3 ) );
- this is equivalent to an 'OR'

2.select re.rest from rest re, restant ra where ra.rest_id = re.rest_id and ant_id = 1 and ant_id = 2;
- this did not work
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

Wrong forum, methinks.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30353
    
150

ant_id = 1 and ant_id = 2;

This is impossible. Ant_id will never be both 1 and 2 for the same record. The following two do what you want:

ant_id in (1, 2);
ant_id = 1 or ant_id = 2;


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: SQL Query Suggestions