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

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProtocolPirate]

That is extremely general and basic information. I didn't see anything there about the unusual kind of join I am trying to do.

I suspect that the only way to do this will be to create a new table that combines all of the essential data from the first two layers (i.e. combine the data in tables a, b & c into a single table). That is an ugly solution though.

 

[PHV]

Another usefull link:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

when multiple tables need to perform a lookup, just use the same table with a different alias--

a
/ \
b c
| |
d1 d2

however, if a-b is one-to-many, and a-c is one-to-many, you have what's called a "fan trap" and will not be able to show these results easily

r937.com | rudy.ca

 

[ProtocolPirate]

The D1, D2 approach will be cumbersome at best. I need to have all of that in one field in my report, not multiplexed across a dozen different select fields, so I would still have to do some programming to get around the issue when generating the report.

Is there a way to do a lookup of a sequence of data in a table? That is, given predefined data, say, key values acquired from a previous pass of another table, and get all of the records associated with that sequence of key fields?

This was an easy problem to solve in dBase. I always disliked SQL, but I never noticed it being so profoundly limited in it's strength...?

 

[r937]

perhaps you do not understand what i meant

d1 and d2 are actually just d

you use aliases in sql to differentiate the rows that match to a versus b

r937.com | rudy.ca

 

[r937]

uh, sorry, typo

you use aliases in sql to differentiate the rows that match to b versus c



r937.com | rudy.ca

 

[ProtocolPirate]

Yes, I know that D1 and D2 refer to the same table, but you can't select based on the table name, nor can you use D as an alias, you can only use D1 and D2 as aliases.

What will happen is that I'll end up with D1 thru D13 as aliases to the Doctors table. At any given moment only one of the intermediate tables will have a doctor's code that gets looked up in the Doctors table, the rest will all be null (i.e. with no code to look up in the Doctors table). But since I can't select Doctor.Name or D.Name, I can only select D1.Name, D2.Name, D3.Name, etc... then later on I'll have to go through in code and strip the 12 out of the thirteen D#.Name fields that are blank.

 

[r937]

you can do it right in the query --

select coalesce(D1.Name, D2.Name, D3.Name, ... ) as doctor_name
  from ...

r937.com | rudy.ca

 

[lespaul]

So you are saying that you have a table set up like:
[tt]
SomeField Doctor1 Doctor2 Doctor3 ....Doctor13[/tt]

and you don't want to join the tableDoctor to all 13 fields? Well, if that is indeed the case, you have committed spreadsheet.

Maybe it will help if you give us some examples of what you have to work with and what you want the results to be.....

you previously say:

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 would do that like this:

SELECT A.*, B.SomeOtherField, DB.DoctorName, C.SomeOtherField, DC.DoctorName
FROM A
INNER JOIN B ON A.SomePK = B.SomePK
INNER JOIN Doctor DB on B.DoctorPK = DB.DoctorPK
INNER JOIN C ON A.SomeK = C.SomeK
INNER JOIN Doctor DC on C.DoctorPK = DC.DoctorPK

Of course I'm just guessing....if you would provide some additional information (like table names (not all 12, just 2 will be enough), fields in those tables, the relationships between the tables and just perhaps some data) along with your expected results, maybe you could get a more definite answer instead of frustrating guesses.



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[r937]

les, my guesses weren't frustrating

at least, not for me! ;-)

r937.com | rudy.ca

 

[r937]

uh oh, wait a sec...

msaccess doesn't support the standard sql COALESCE function





r937.com | rudy.ca

 

[lespaul]

Rudy,
I was thinking that our guesses were frustrating the OP!!!
Les

 

[r937]

well, that would be poetic justice

the question i answered involved tables a, b, c, and d

not 12 tables with doctors!!

whose fault is the frustration?

;-)

r937.com | rudy.ca

 

[ProtocolPirate]

Coalesce sounds like the ticket, if it works with Pervasive SQL! Thank you very much!

well, that would be poetic justice

the question i answered involved tables a, b, c, and d

not 12 tables with doctors!!

Well I simplified for brevity, although I did mention that tables b & c were two of a dozen.

The root table is called Daysheet and has global financial data. The intermediate tables are all called Posting followed by an underscore and a location name, such as Posting_Wilshire. The posting tables all contain doctor’s codes, such as SS01234. The Doctors table has doctors’ codes in the Id field, and their names in the Name field.

So the links are from Daysheet in ChargeCode to ChrageCode in the Posting tables, and then from the doctor's code in the Posting tables to the Id field in the Doctors table.

All I’m trying to do here is get all of the doctor’s names, along with financial data about their referrals. Unfortunately Daysheet does not have doctor codes, or this would be extremely easy.

 

[ProtocolPirate]

I meant:

So the links are from ChargeCode in Daysheet too ChargeCode in the Posting tables, and then from the doctor's code in the Posting tables to the Id field in the Doctors table.

 

[ProtocolPirate]

Hmmmm... the syntax seems to be OK, but the query never returns.

The linking fields are all indexed. SQL really seems to dislike doing this...

If there was a cost effective way to simply do it with two passes, collecting all the data except the doctor names the first pass, and then substituting the doctor codes for names in the second pass, I'd be very happy with such a solution.

 

[r937]

instead of this --

     a
  /  |  \  \
 b   c   d   e ...
 |   |   |   |
d1  d2  d3  d4 ...

you want this --

     a
     |
 ( b+c+d+e+... )
     |
     D

do a UNION on all the posting tables



r937.com | rudy.ca

 

[ProtocolPirate]

Thank you very much! Union is the ANSWER!

It not only doesn't hang, but it runs reasonably fast!