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


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
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 :
 
 
subject: Getting one Row from JOIN query