Joining with multiple aliases for look up tables

[joewkelly]

Hello community,

I'm trying to do this:

Select a.*,

b.return_code as return_code1

c.return_code as return_code2

...

From Core_Data as a

Left Lookup_Table1 as b

  on a.key1 = b.key

Left Lookup_Table1 as c

  on a.key2 = c.key

...

I have two lookup tables, that I need to join about 30 times each.  In healthcare, you can have multiple diagnosis codes and multiple procedure codes. 

When I use a Join Multiple, it wants me to select for each record a value for each table.  So, if I say a.key, it will ask me what both tables b and c should be matching on.  But I want to join a to b, regardless of what is in table c and separately a to c, regardless of what is in table b.

In this case, will I need to do 60 different single-joins (and 60 unions for left joins)?

Best wishes,

Joe Kelly

[Joe_Mako]

Hi Joe,

Can you please provide some sample data that represents your situaiton, and what you expect the result to be?

My iniital impression is that Join Mulitple would do what you are asking for, but I may be misundersting your prhasing, and example data in and expected result out would make your request clear. Thank you!

[joewkelly]

Good afternoon Joe,

I found a flaw in my SQL, which may be the source of the confusion, table a, Core_Data, has multiple keys -- key1 is different from key2, representing the different diagnosis codes

Core_Data

patient id, key1, key2, key3 ....

ABC, 100, 101, 200...

DEF, 201, 300, 301...

Lookup_Table1

key, return_code

100, 1

101, 1

200, 2

201, 2

300, 3

301, 3

...

Joined data:

patient id, key1, key2, key3...  return_code1, return_code2, return_code_3 ...

ABC, 100, 101, 200, ... , 1, 1, 2

DEF, 201, 300, 301, ... , 2, 3, 3

When I do the multiple join, what I'm looking for is something like this:

Input_#1, <--> Input_#2 <--> Input_#3 <--> Input_#4

key1, key, <blank>, <blank>

key2, <blank>, key, <blank>

...

But, I don't see the <blank> option now that I have connected all of my inputs.

Thank you!

[Joe_Mako · Accepted solution]

How about the attached?

The Select tools after input are to change the values to strings, and may not be necessary with your real data, the Transpose of all the key values in Core_Data will enable a single Join Multiple (effectively a left join in this case), the Formula tool creates the field names for the return codes, two Cross Tab tools will make the data wide again, and a Join to bring all the data back together.

[joewkelly]

That should work!   Let me give it a try!