Join from 1 -> many -> 1? - 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)

Join from 1 -> many -> 1?

Author

Topic

Mike Reynolds
Starting Member

2 Posts

Posted - 2007-11-21 : 18:29:34

I need to join one table with about a dozen others, but in those dozen other tables is a field that must be looked up in yet another table. Imagine a diamond:
b
/ a d
\ /
c

Where 'a' is the first table, 'b' & 'c' are two of the dozen other tables, and 'd' is the last table that all of the dozen intermediate tables have to make a lookup into.

I tried writing an sql join with a switch statement setting the Id field in table 'd' that returns which ever DoctorCode in tables 'b' or 'c' (or any of the other dozen) is <> "", but I got a syntax error on it.

Can this be done in SQL? If not, is there a way to execute SQL statements that do seeks in a table based on a sequence of data that you give it (i.e. so I can programmatically control this from VB.NET)?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-21 : 18:39:09

You really need to give more specific info, some examples of the tables and their columns, but here is what it sounds like you want to do:

SELECT ...
FROM a
INNER JOIN b ON b.PK = a.col1
INNER JOIN c ON c.PK = a.col2
INNER JOIN d D1 ON D1.PK = b.col2
INNER JOIN d D2 ON D2.PK = c.col2

Note that I used table d twice and gave it two different aliases (D1 and D2) in order to do that.

Mike Reynolds
Starting Member

2 Posts

Posted - 2007-11-21 : 18:42:06

Lets try that diamond shape again...

...b
../ a....d
..\ /
...c

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-21 : 19:03:53

Yeah, that's not helping...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 19:14:30

So b joins to a and c
c only joins to a
a only joins to d


Select m.col1,m.col2 b.col, c.col,
FROM b inner join (Select a.col1, b.col2
                    FROM a inner join d on a.ColID = d.ColID) m
     c inner join m on c.colID = m.colid

Do the a=d join as a derived table, then join the others to that where appropriate.

And yes, the diamond shape isn't helping.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources