• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting one Row from JOIN query

 
Atul Mishra
Ranch Hand
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you really dont care which of the matching rows you get , then try something like :
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic