Joins Question

I have 3 tables. Table1(time,readingA) Table2(time,readingB)
table3(time,readingC)
Now the time can be same and it can be different. Now i want to know
how do i join so that i get the data: time,readingA,readingB,readingC
If the time is same then it is fine, but if the time is not same in
two tables for eg : if table1 has a record for time 12:30 and Table2
and table3 does not have that time then it should show data from
table1 and the readingB and readingC will be blank.
I hope my question is clear.
Thanks for helpHi
If you are not interested in the time then you may not want to truncate
everything to midnight when they are inserted (which if you don't have a tim
e
portion on your date/time will happen anyhow). The isssue then is what will
happen if there are multiple records for each day? If the time is require fo
r
some other reason you can also use the convert function to compare the date
part of the datetime
SELECT CONVERT(char(8),T1.time,112) AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
JOIN Table2 T2 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T2.time,112
)
JOIN Table3 T3 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T3.time,112
)
John
"Pradeep" wrote:

> I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help
>|||Hi,
if I understand correctly your question, you need to use left join.
Something like
SELECT T1.time AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2
ON T2.time=T2.time
LEFT JOIN Table3 T3
ON T1.time= T3.time
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502012334.5b8f2955@.posting.google.com...
>I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help|||It looks like I may have got this mixed up! As Ana says use left JOIN
although you may not want your times to 3/100 of a second, in which
case you will still need to truncate them
SELECT T1.Time, T1.readingA, T2=AD.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.time =3D T2.time
LEFT JOIN Table3 T2 ON T1.time =3D T3.time
John

Join Problem

Hello, I have 3 tables in my datasource and I am trying to Join them with the
following
{ oj TABLE2 RIGHT OUTER JOIN
TABLE3 TABLE1 ON TABLE2.ACCTNO = TABLE1.ACCTNO }
At least this is what the designer translates it to. I basically want
Table1 as the primary table (all records) and table 2 and table3 are just
little look up tables for parameters.
When I do this I get an ODBC error,
'Token was not valid: Vaid Tokens: on left join inner joins exception.'
At this point if there is no matching key field in table3 it is not showing
me that record.. And I need to see it. HelpI am accessing an AS400 and it looks like an ODBC problem so I will create
the base query and get rid of the SQL joins.
"SLB" wrote:
> Hello, I have 3 tables in my datasource and I am trying to Join them with the
> following
> { oj TABLE2 RIGHT OUTER JOIN
> TABLE3 TABLE1 ON TABLE2.ACCTNO = TABLE1.ACCTNO }
> At least this is what the designer translates it to. I basically want
> Table1 as the primary table (all records) and table 2 and table3 are just
> little look up tables for parameters.
> When I do this I get an ODBC error,
> 'Token was not valid: Vaid Tokens: on left join inner joins exception.'
> At this point if there is no matching key field in table3 it is not showing
> me that record.. And I need to see it. Help
>|||try creating a view and selecting from that view. I do that on SRS somtimes
when .net tries to rewrite my query to often. That way you can do what you
want on the database side and it seems to run faster also.
SLB wrote:
>I am accessing an AS400 and it looks like an ODBC problem so I will create
>the base query and get rid of the SQL joins.
>> Hello, I have 3 tables in my datasource and I am trying to Join them with the
>> following
>[quoted text clipped - 10 lines]
>> At this point if there is no matching key field in table3 it is not showing
>> me that record.. And I need to see it. Help
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Join multiple tables

Hi
I'm new to SQL and I have the following problem.
I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), table3
(a3, b3, c3). I have to build joined table with columns like this:
col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3).
a1=a2=a3, b1=b2=b3
How to build query for this?
Thanks in advance.Try,
SELECT T1.a1, T1.a2, T1.c1, T2.c2, T3.c3
FROM T1
JOIN T2 ON T1.a1 = T2.a2 AND T1.b1 = T2.b2
JOIN T3 ON T1.a1 = T3.a3 AND T1.b1 = T3.b3
BG, SQL Server MVP
www.SolidQualityLearning.com
"GrzesB" <GrzesB@.discussions.microsoft.com> wrote in message
news:866BC885-A828-4758-AA23-DE5DC84C6DE0@.microsoft.com...
> Hi
> I'm new to SQL and I have the following problem.
> I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2),
> table3
> (a3, b3, c3). I have to build joined table with columns like this:
> col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2),
> col5(c3).
> a1=a2=a3, b1=b2=b3
> How to build query for this?
> Thanks in advance.
>|||It will help us to understand better your request if you post DDL, sample
data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"GrzesB" wrote:

> Hi
> I'm new to SQL and I have the following problem.
> I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), tabl
e3
> (a3, b3, c3). I have to build joined table with columns like this:
> col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3
).
> a1=a2=a3, b1=b2=b3
> How to build query for this?
> Thanks in advance.
>