Data Issues Very frustrated

I have various sql, sybase & access databases that i need to report from onto
the 1 table. A number of joins link them all together. As you can only use 1
dataset, what would be the best way to report on my data into the one table.
I have spent weeks trying to work this out. ThanksYou could use a stored procedure or view to do all the joins and then call
the one view or stored procedure from your dataset.
John K.
===
"Tango" wrote:
> I have various sql, sybase & access databases that i need to report from onto
> the 1 table. A number of joins link them all together. As you can only use 1
> dataset, what would be the best way to report on my data into the one table.
> I have spent weeks trying to work this out. Thanks|||Thanks John,
Whats your preference here. Id prefer to only have to research 1 options as
time is limited at this stage. Let me know if you need any more info.
"John K" wrote:
> You could use a stored procedure or view to do all the joins and then call
> the one view or stored procedure from your dataset.
> John K.
> ===> "Tango" wrote:
> > I have various sql, sybase & access databases that i need to report from onto
> > the 1 table. A number of joins link them all together. As you can only use 1
> > dataset, what would be the best way to report on my data into the one table.
> > I have spent weeks trying to work this out. Thanks

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)