Update syntax - scratching head!!

[UHNSTrust]

I have 2 tables that have the same structure. What I want to do is combine TableA with TableB to show the following:

TableA

BedNo    Time1    Time2    Time3    Time4
   1                 1        1      
   2        1                 2         2
   3                 3                  4

TableB

BedNo    Time1    Time2    Time3    Time4
   1        3                           9
   2                 6
   3        6                 3         4

I would like to see the following returned by a SP

BedNo    Time1    Time2    Time3    Time4
   1        3        1        1         9
   2        1        6        2         2
   3        6        3        3         4

Is this possible (easily)?

Any help would be appreciated.

Thanks

Jonathan

 

[vongrunt]

Assuming that blanks are NULLs:

select
isnull(A.Time1, B.Time1) as Time1,
isnull(A.Time2, B.Time2) as Time2,
isnull(A.Time3, B.Time3) as Time3,
isnull(A.Time4, B.Time4) as Time4
from TableA A
inner join TableB B on A.BedNo=B.BedNo
order by A.BedNo

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[clapag22]

vongrunt's is more elegant than mine, but since I typed it already I'll post it anyway...

So if data exists for BedNo1/Time1 in TableA is it always true that data wouldn't exist for BedNo1/Time1 in TableB? If that's the case, then what I would do is to make a view that's a union of the two tables:

create view v_my_view as
select *
from TableA
union all
select *
from TableB
GO

Then write this query:

select BedNo, max(time1) as time1, max(time2) as time2,
max(time3) as time3, max(time4) as time4
from v_my_view
group by BedNo

Craig

 

[UHNSTrust]

Vongrunt.

Thanks for the speedy reply. This is exactly what I needed.

Things are so simple when you know what you are doing (I'll keep learning)!

Jonathan

 

[UHNSTrust]

Craig,

Thanks for your reply.

Jonathan