Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
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

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

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

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 :
I agree. Here's the link:
subject: Getting one Row from JOIN query
It's not a secret anymore!