stored procedure advice 1

[crystalized]

I would like to know if anyone can help me with a stored procedure I need to complete.

The problem is this:
I have table1 with an ID and Name (and other misc fields)
I have table2 with an ID and Name and ForiegnKey

I want to select the ID and name from each of the tables, and the Id and names from table 2 should be selected where the foriegn key is the parameter of the query.

I need the end result to be one result set (to be used in a combo box)

I know how to place 2 select statements that get me the results I want when I run the stored procedure. But they come out as 2 seperate result sets (so only the first one shows up in the combo box).

This is the basic set up for the stored procedure that I created for my testing purposes

[tt]
create procedure stpTest
(
[tab]@nFirmID int
)
as
[tab]select frName as nameOf,frID as ID from tblOne

[tab]select fawName as nameOf,fawID as ID from tblTwo
[tab]where fawFKID=@nFirmID

return
[/tt]

But as I stated above I get two result sets this way and only the first select from tblOne gets into the combo box(ASP combo box).

How can I bring the two things together to form one result set. I must admit that as stored procedures go I have done only the very basic stored procedures that basically select from one table. So I know that the above example is probably not even close to right, but if someone can set me on the right path I would really appreciate it.

Thanks in advance

 

[MalcolmW]

create procedure stpTest
(
@nFirmID int
)
as
select frName as nameOf,frID as ID from tblOne
UNION
select fawName as nameOf,fawID as ID from tblTwo
where fawFKID=@nFirmID

return

 

[crystalized]

Thanks a bunch Malcolm

I really appreciate your help