Getting one Row from JOIN query

[Atul Mishra]

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]

If you really dont care which of the matching rows you get , then try something like :

  
select t2.firstname,
         t2.lastname 
from
       table1 t1,
       table2 t2 
where
           t1.state = t2.state
    AND  t1.ID     =  t2.ID
    AND rownum   =   1