Data from three Tables (Joins)

Thanks for your responses.
Well the time can be same and cannot be same
For ex:
Table1
(timestamp,readingA)
(2/2/2005 12:09:26,123)
(2/2/2005 12:19:26,324)
Table2
(timestamp,readingB)
(2/2/2005 12:09:26,321)
(2/2/2005 12:29:26,657)
Table3
(timestamp,readingC)
(2/2/2005 12:09:26,231)
(2/2/2005 12:39:26,987)
Result Desired
(timestamp,readingA,readingB,readingC)
2/2/2005 12:09:26,123,321,231
2/2/2005 12:19:26,324,,
2/2/2005 12:29:26,,657,
2/2/2005 12:39:26,,,987
AS you can see the timestamp which is common in all tables is
displayed once alongwith its readings, but the one which is not common
is displayed alongwith its readings while other readings are blank.
I hope now it is clearThis is a typical xtab problem. Here is a trick.
select timestamp,max(case when tb=1 then reading else 0 end) readingA,
max(case when tb=2 then reading else 0 end) readingB,
max(case when tb=3 then reading else 0 end) readingC
from(
select *,1
from tb1
union all select *,2
from tb2
union all select *,3
from tb3) derived(timestamp,reading,tb)
group by timestamp
Do check out these:
http://support.microsoft.com/kb/175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/
-oj
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502022352.7cddb731@.posting.google.com...
> Thanks for your responses.
> Well the time can be same and cannot be same
> For ex:
> Table1
> (timestamp,readingA)
> (2/2/2005 12:09:26,123)
> (2/2/2005 12:19:26,324)
> Table2
> (timestamp,readingB)
> (2/2/2005 12:09:26,321)
> (2/2/2005 12:29:26,657)
> Table3
> (timestamp,readingC)
> (2/2/2005 12:09:26,231)
> (2/2/2005 12:39:26,987)
> Result Desired
> (timestamp,readingA,readingB,readingC)
> 2/2/2005 12:09:26,123,321,231
> 2/2/2005 12:19:26,324,,
> 2/2/2005 12:29:26,,657,
> 2/2/2005 12:39:26,,,987
> AS you can see the timestamp which is common in all tables is
> displayed once alongwith its readings, but the one which is not common
> is displayed alongwith its readings while other readings are blank.
> I hope now it is clear|||On 2 Feb 2005 23:52:26 -0800, Pradeep wrote:

>Thanks for your responses.
>Well the time can be same and cannot be same
>For ex:
>Table1
>(timestamp,readingA)
>(2/2/2005 12:09:26,123)
>(2/2/2005 12:19:26,324)
>Table2
>(timestamp,readingB)
>(2/2/2005 12:09:26,321)
>(2/2/2005 12:29:26,657)
>Table3
>(timestamp,readingC)
>(2/2/2005 12:09:26,231)
>(2/2/2005 12:39:26,987)
>Result Desired
>(timestamp,readingA,readingB,readingC)
>2/2/2005 12:09:26,123,321,231
>2/2/2005 12:19:26,324,,
>2/2/2005 12:29:26,,657,
>2/2/2005 12:39:26,,,987
>AS you can see the timestamp which is common in all tables is
>displayed once alongwith its readings, but the one which is not common
>is displayed alongwith its readings while other readings are blank.
Hi Pradeep,
Try if this accomplishes what you want:
SELECT COALESCE (A."timestamp", B."timestamp", C."timestamp"),
A.readingA, B.readingB, C.readingC
FROM Table1 AS A
FULL OUTER JOIN Table2 AS B
ON B."timestamp" = A."timestamp"
FULL OUTER JOIN Table3 AS C
ON C."timestamp" = COALESCE (A."timestamp", B."timestamp")
(untested)
By the way, I suggest you use another name for the timestamp column, as
timestamp is a reserved word in SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)