posted 14 years ago
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