query multiple tables and combine results into one return table for stored procedure?

Question

I have several different tables, but they all have 2 columns that are the same name. I want to write a stored procedure that searches one column in all of the tables and returns the result. Ideas? I'm fairly nub when it comes to SQL.

Answer 1

Select myColumn FROM Table1
UNION Select myColumn FROM Table2
UNION Select myColumn FROM Table3 

..etc

-- Note all column names have to be the same and have to be in each table for this to work

Answer 2

You wouldn't even need a stored procedure...just use a union query.

select field1, field2 from table1 where table1.field1=criteria
union
select field1, field2 from table2 where table2.field1=criteria
union
select field1, field2 from table3 where table3.field1=criteria
etc...

Answer 3

The operation you are looking for is UNION or UNION ALL.

SELECT * FROM (
 SELECT col1, col2 FROM table1
 UNION ALL
 SELECT col1, col2 FROM table2
 UNION ALL
 SELECT col1, col2 FROM table3
) all_tables
WHERE all_tables.col1 = 'something'

If you use UNION rather than UNION ALL, the database will eliminate duplicate rows that may be in multiple tables. If you know there won't be any duplicates, use UNION ALL since it is generally much faster.