Join from 1 -> many -> 1 tables? 1

[ProtocolPirate]

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.

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)?

 

[ProtocolPirate]

Oh, but is there a way to have all the posting fields link into the doctors table with a single union? What works for me right now is a dozen repetitions of almost identical queries unioned together:

SELECT Daysheet.LocCode, D1.Name FROM Daysheet
INNER JOIN Posting_Wilshire ON Posting_Wilshire.ChargeNumber = Daysheet.ChargeNum
INNER JOIN Servloc D1 ON D1.Id = Posting_Wilshire.RefPhyCode
where Daysheet.LocCode = 11
UNION
SELECT Daysheet.LocCode, D1.Name FROM Daysheet
INNER JOIN Posting_Downey ON Posting_Downey.ChargeNumber = Daysheet.ChargeNum
INNER JOIN Servloc D1 ON D1.Id = Posting_Downey.RefPhyCode
where Daysheet.LocCode = 4
UNION
SELECT Daysheet.LocCode, D1.Name FROM Daysheet
INNER JOIN Posting_Downey ON Posting_Downey.ChargeNumber = Daysheet.ChargeNum
INNER JOIN Servloc D1 ON D1.Id = Posting_Downey.RefPhyCode
where Daysheet.LocCode = 12

 

[r937]

you're joining Servloc once per Posting table

do the union, then join to Servloc only once

SELECT Daysheet.LocCode
     , D.Name 
  FROM Daysheet
INNER 
  JOIN (
       SELECT ChargeNumber 
            , RefPhyCode
         FROM Posting_Wilshire
       UNION ALL
       SELECT ChargeNumber 
            , RefPhyCode
         FROM Posting_Downey
       UNION ALL
       ...
       ) AS All_da_Postings
    ON All_da_Postings.ChargeNumber = Daysheet.ChargeNum 
INNER 
  JOIN Servloc D
    ON D.Id = All_da_Postings.RefPhyCode

r937.com | rudy.ca

 

[ProtocolPirate]

(I didn't get any post notification for this last message.)

Thanks a million! That is fantastic!