CODEWITHSUNDEEP
sqlstored-proceduresselect
Shawn
Shawn

Reputation: 2366

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

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.

Upvotes: 1

Views: 6937

Answers (3)

Eric Petroelje
Eric Petroelje

Reputation: 60498

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.

Upvotes: 7

Paul Abbott
Paul Abbott

Reputation: 7211

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...

Upvotes: 1

Eden
Eden

Reputation: 543

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

Upvotes: 1

Related Questions