3 Table join question

3 Table join question.
Here are 3 tables.
T1
--
ID VAL
A 0
B 0
C 0
D 0
E 0
T2
--
ID METER
A 1
X 1
B 1
Y 1
T3
--
ID NUM
B 2
H 2
R 2
J 2
K 2
X 2
E 2
I was reading a tutorial that said you can join the three tables in these
ways:
T1 joins T2 which joins T3
T1 joins T2 and the Join of T1 and T2 joins T3
T1 joins T2 and T1 joins T3
The tutorial doesn't give any examples.
Which SQL statements would represent these three types of multiple table
joins?
GeoffGeoff,
T1 JOIN T2
ON T1.ID = T2.ID
JOIN T3
ON T2.ID = T3.ID
However note that the order in which the tables are joined will be
determined by SQL Server not the order in which the tables are listed in the
FROM clause unless the the SET FORCEPLAN statement is used.
HTH
J
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>3 Table join question.
> Here are 3 tables.
>
> T1
> --
> ID VAL
> A 0
> B 0
> C 0
> D 0
> E 0
>
> T2
> --
> ID METER
> A 1
> X 1
> B 1
> Y 1
>
> T3
> --
> ID NUM
> B 2
> H 2
> R 2
> J 2
> K 2
> X 2
> E 2
>
> I was reading a tutorial that said you can join the three tables in these
> ways:
> T1 joins T2 which joins T3
> T1 joins T2 and the Join of T1 and T2 joins T3
> T1 joins T2 and T1 joins T3
> The tutorial doesn't give any examples.
> Which SQL statements would represent these three types of multiple table
> joins?
> Geoff|||I suppose your answer is an example of (1)?
(1) T1 joins T2 which joins T3
(2) T1 joins T2 and the Join of T1 and T2 joins T3
(3) T1 joins T2 and T1 joins T3
Geoff
"Jerry Spivey" wrote:

> Geoff,
> T1 JOIN T2
> ON T1.ID = T2.ID
> JOIN T3
> ON T2.ID = T3.ID
> However note that the order in which the tables are joined will be
> determined by SQL Server not the order in which the tables are listed in t
he
> FROM clause unless the the SET FORCEPLAN statement is used.
> HTH
> J
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>
>|||Probably. Depends on how the optimizer chooses to join the tables. It may
join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
intermediate result set to 1.
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>I suppose your answer is an example of (1)?
> (1) T1 joins T2 which joins T3
> (2) T1 joins T2 and the Join of T1 and T2 joins T3
> (3) T1 joins T2 and T1 joins T3
> Geoff
> "Jerry Spivey" wrote:
>|||O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
implemented the JOIN for three tables. The results from each of the three
"ways" are the same.
I read the statement as "These are the three ways to join tables" and you
pick the type of join you want.
I was playing with this as an example of the (3)rd way
SELECT *
FROM (SELECT T1.ID, T1.VAL, T2.METER
FROM T1, T2
WHERE T1.ID = T2.ID) B INNER JOIN
(SELECT T3.ID, T3.NUM
FROM T1, T3
WHERE T1.ID = T3.ID) A ON B.ID = A.ID
I guess I missed the point of the statement in the tutorial.
Thanks for the clarification,
Geoff
"Jerry Spivey" wrote:

> Probably. Depends on how the optimizer chooses to join the tables. It ma
y
> join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
> intermediate result set to 1.
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>
>|||An example of (2) is
(T1 JOIN T2 ON T1.ID = T2.ID) JOIN T3 ON T2.ID = T3.ID
By using parenthesis, you can force the order in which the tables are
joined, creating a scenario in which T1 joins T2 and T3 joins the join of T1
and T2.
An exapmle of (3) is
T1
JOIN T2 ON T1.ID = T2.ID
JOIN T3 ON T1.ID = T3.ID
I think that (1) and (3) are actually just different orders of the same JOIN
statement.
Chris
"Geoff" wrote:
> O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
> implemented the JOIN for three tables. The results from each of the three
> "ways" are the same.
> I read the statement as "These are the three ways to join tables" and you
> pick the type of join you want.
> I was playing with this as an example of the (3)rd way
> SELECT *
> FROM (SELECT T1.ID, T1.VAL, T2.METER
> FROM T1, T2
> WHERE T1.ID = T2.ID) B INNER JOIN
> (SELECT T3.ID, T3.NUM
> FROM T1, T3
> WHERE T1.ID = T3.ID) A ON B.ID = A.ID
>
> I guess I missed the point of the statement in the tutorial.
> Thanks for the clarification,
> Geoff
>
> "Jerry Spivey" wrote:
>