How do I query to separate tables?

Question

Post reply

How do I query to separate tables?

Anthony Phillips-301303

Mr or Mrs. 500

Points: 574
More actions
March 10, 2006 at 10:00 am

#168010

I have a question. I have two tables. Once called ItemTableOne and the other ItemTableTwo. Is it possible to create a stored procedure that will query the first table, and if the desired record is not found in the first table it then querys the second table? What would it look like?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can do it in a single with something like this:

declare @somekey int

set @somekey=1

if exists(select * from ItemTableOne where someval=@somekey)

select * from ItemTableOne where someval=@somekey

else

select * from ItemTableTwo where someval=@somekey

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JeffB Hall of Fame Points: 3541 More actions · Answer 2

You can also try:

declare @Table1 table (someval int, fname varchar(20))

declare @Table2 table (someval int, fname varchar(20))

insert @Table1 values(1, 'Jeff')

insert @Table1 values(2, 'James-1')

insert @Table2 values(2, 'James-2')

insert @Table2 values(3, 'Ed')

select coalesce(t1.fname, t2.fname)

from @table1 t1

full outer join @table2 t2

on t1.someval = t2.someval

where coalesce(t1.someval, t2.someval) = 2

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

If you want to treat the two different tables as one, UNION (or UNION ALL) is one way to do it.

(borrowing Jeff's example)

declare @Table1 table (someval int, fname varchar(20))

declare @Table2 table (someval int, fname varchar(20))

insert @Table1 values(1, 'Jeff')

insert @Table1 values(2, 'James-1')

insert @Table2 values(2, 'James-2')

insert @Table2 values(3, 'Ed')

select fname from @Table1 where someval = 3

UNION

select fname from @Table2 where someval = 3

/Kenneth

[KH] SSCommitted Points: 1908 More actions · Answer 4

use @@rowcount

select * from table1

if @@rowcount = 0

begin

select * from table2

end