Getting returns from two SQL tables

[Bene1]

I have two SQL tables that hold zipcode info. One is for US and one is for Canada. I am working on a stored procedure that has a parameter @city, and then returns the City, State, and Zip from each table

WHERE (City = @city)

. I can get it working for one table or the other, but not both (unless I return 6 fields, one US.City then Can.City).

I really want to know if there is a way to load all info returned from both tables into common field names, so that I could display 3 columns instead of 6.

Any help would be appreciated... thanks in advance.

 

[RickCole]

Is it something like this your after:

select city,state,zip
from US_table where city = @city
union
select city,state,zip
from Canada_table where city = @city


Rick.

 

[Bene1]

Thanks... this is exactly what I wanted!!!!