File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Getting one Row from JOIN query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Getting one Row from JOIN query" Watch "Getting one Row from JOIN query" New topic
Author

Getting one Row from JOIN query

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Hi all,

I need to JOIN 2 tables. the second table can have more than one rows which can match the selection. How do I limit the JOIN to only return one row per ID ?

Table 1 holds information

State ID
CA 123
TX 456

Table 2 can have multiple rows for state and ID.
firstnme, lastname, state,ID
firstname,lastname,state,ID.

since the second table is kind of a logger, it can have multiple rows with same state+ID.

If I do, select table2.firstname,table2.lastname
where (state,ID) in (select state,ID from table 1)

it returns multiple results. How can I include a count, and return a row based on the count

Thanks
Atul
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
If you really dont care which of the matching rows you get , then try something like :
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Getting one Row from JOIN query
 
Similar Threads
oracle sql - case statement and count
Search Query Problem
Hibernate: Different Join in Save() and Get()
Resultset processes 1 record
How to find no of Rows in my ResultSet