File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Getting one Row from JOIN query
Joined: Jun 08, 2006
Oct 06, 2009 07:08:01
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
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
Joined: Jan 24, 2006
Oct 06, 2009 14:51:27
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
It is sorta covered in the
JavaRanch Style Guide
subject: Getting one Row from JOIN query
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
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014