Multiple Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Select

Author

Topic

rbharatha
Starting Member

29 Posts

Posted - 2002-01-24 : 20:20:09

Hi Sql Team,

I have to select 3 columns to display from three different tables.,

for ex : -

i have to get the column values from the First Table if it is present in it, if not then from the Second Table or Last i have to get the default value from the third table.

and match with fourth table. for all the four tables one column is common.

with regards

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-24 : 21:04:27

Hi rbharatha

I'm not sure if I entirely understand - but I think this is what you want:

my example uses four tables ta, tb and tc with the table holding common keys td.

I use three temp tables, #ta, #tb and #tc

select td.num, ta.val into #ta
from tD inner join tA on td.num = ta.num

select td.num, tb.val into #tb
from tD inner join tb on td.num = tb.num
where td.num not in (select num from #ta)

select td.num, tc.val into #tc
from tD inner join tc on td.num = tc.num
where td.num not in (select num from #ta)
and td.num not in (select num from #tb)

select num, val from #ta
union
select num, val from #tb
union
select num, val from #tc

drop table #ta
drop table #tb
drop table #tc

dirty - someone out there may come up with a much better solution - but that should do what you want...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 01/24/2002 21:05:01

rbharatha
Starting Member

29 Posts

Posted - 2002-01-24 : 21:39:39

hi rrb,

thanks for the solution provided, it served my problem.,

have a nice day

with regards
Ramesh

rbharatha
Starting Member

29 Posts

Posted - 2002-01-24 : 22:09:41

hi Sql Team,

My problem is that, i want to select column value from the first table if it is present, else from second table or default from the third table, then
I want to update the another table with the selected column from the output generated from the #temp tables, how can i do it.
here is the query. for your reference which i have written.,
if iam wrong correct me.,

select td.account_id, ta.fleet_id, ta.mem_id, ta.phone_number into #ta
from instalment_detail td inner join mibas..curr_month_fixed ta on td.account_id = ta.account_id

select td.account_id, tb.fleet_id, tb.mem_id, tb.phone_number into #tb
from instalment_detail td inner join mibas..last_month_backward tb
on td.account_id = tb.account_id where td.account_id not in (select account_id from #ta)

select td.account_id, tc.mbs_fleet_id, tc.mbs_member_id, tc.mbs_phone_no, order_status,
obsolete into #tc from instalment_detail td inner join custcare..unit tc on td.account_id = tc.account_id
where td.account_id not in (select account_id from #ta) and tc.order_status <> 'X'
and tc.obsolete = 'N' and td.account_id not in (select account_id from #tb)

select account_id, fleet_id, mem_id, phone_number from #ta
union
select account_id, fleet_id, mem_id, phone_number from #tb
union
select account_id, mbs_fleet_id, mbs_member_id, mbs_phone_no from #tc

update invoicetest..instalment_bill set inv_fleet_id = isnull(isnull(ta.fleet_id,tb.fleet_id),tc.mbs_fleet_id)
from #ta, #tb,#tc

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-01-24 : 22:12:05

I believe this will do what you want...

Select
td.ID,
coalesce(ta.col1, tb.col1, tc.col1),
coalesce(ta.col2, tb.col2, tc.col2),
coalesce(ta.col3, tb.col3, tc.col3)
From Table4 as td
Left Join Table1 as ta
On td.ID =ta.ID
Left Join Table2 as tb
On td.ID =tb.ID
Left Join Table3 as tc
On td.ID =tc.ID

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-01-24 : 22:14:36

Your last message was posted whilst I was writing my previous reply,
I think this is what you want ...

Insert into td
(col1, col2, col3)
Select
coalesce(ta.col1, tb.col1, tc.col1),
coalesce(ta.col2, tb.col2, tc.col2),
coalesce(ta.col3, tb.col3, tc.col3)
From Table4 as td
Left Join Table1 as ta
On td.ID =ta.ID
Left Join Table2 as tb
On td.ID =tb.ID
Left Join Table3 as tc
On td.ID =tc.ID

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-01-24 : 22:17:54

OK - third time lucky

Need to use Update not Insert !! Doh !!

Update Table4
Set
col1 =coalesce(ta.col1, tb.col1, tc.col1),
col2 = coalesce(ta.col2, tb.col2, tc.col2),
col3 = coalesce(ta.col3, tb.col3, tc.col3)
From Table4 as td
Left Join Table1 as ta
On td.ID =ta.ID
Left Join Table2 as tb
On td.ID =tb.ID
Left Join Table3

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-01-24 : 22:47:43

i have a other option i dont know how well it works

Select
isnull(isnull(ta.col1, tb.col1), tc.col1),
isnull(isnull(ta.col2, tb.col2), tc.col2),
isnull(isnull(ta.col3, tb.col3), tc.col3),
From Table4 as td
Left Join Table1 as ta
On td.ID =ta.ID
Left Join Table2 as tb
On td.ID =tb.ID
Left Join Table3 as tc
On td.ID =tc.ID

will there be any diff in performance

just a guess

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources