Rookie SQL Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rookie SQL Question

Author

Topic

Wanabe
Starting Member

7 Posts

Posted - 2009-02-18 : 19:04:23

I have 3 tables. There is a direct relationship between table 1 and table 2. There is also a direct relationship between table 2 and table 3. No relationship between table 1 and 3.

Is there a way to link all 3 tables together so that they are all included in my data set in a single query? I'm used to seeing join statements such as:
FROM table 1
INNER JOIN table 2....

How can I include my 3rd table if there is no direct relationship between table 3 and table 1?

Thanks!

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 19:15:38

There are a lot of pieces missing from your question. I should probably tell you to post more specifics, i.e. table definition, sample rows, and expected results. But I'm going to start with a general response. If it doesn't help 100%, then please respond with more specifics. In this case probably just the table definitions from all 3 tables would suffice (or at least the columns from each table that make up the relationships).

But let me offer an example and see if it makes sense:

create table person
(personid int,
name varchar(100))

create table orderhead
(ohid int,
personid int,
orderdate datetime)

create table orderdetail
(odid int,
ohid int,
itemid int,
itemdescription varchar(100))

You can certainly join all 3 of these tables in order to see the Name, Order header information and Order detail information all in a single row. The join would look like this:

select p.id, p.name, o.ohid, o.orderdate, od.itemid, od.itemdescription
from person p
inner join orderhead o on p.personid = o.personid
inner join orderdetail od on o.ohid = od.ohid

Link Person to OrderHead, and OrderHead to OrderDetail. Person is effectively linked to OrderDetail. Imagine the tables represented on a diagram with the join links drawn between them. The path that takes you from Person to OrderDetail (via OrderHead) is what people mean when they refer to the "join path".

Hope this helps.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 23:50:13

quote:
Originally posted by Wanabe

I have 3 tables. There is a direct relationship between table 1 and table 2. There is also a direct relationship between table 2 and table 3. No relationship between table 1 and 3.

Is there a way to link all 3 tables together so that they are all included in my data set in a single query? I'm used to seeing join statements such as:
FROM table 1
INNER JOIN table 2....

How can I include my 3rd table if there is no direct relationship between table 3 and table 1?

Thanks!

then u can use the join condition for 2nd table to 3rd table
table1.col1 = table2.col1
join table3 on table3.col1 = table2.col2....
see the example given by sqlforgirls

Wanabe
Starting Member

7 Posts

Posted - 2009-02-19 : 11:28:33

Thanks so much. This answers my question exactly. I guess I was looking at JOINS incorrectly. I was thinking that each JOIN statement had to reflect a direct relationship with the table.column listed directly after the FROM statement. I'll try this out and let you know how it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources