Bringing a field from one table to another (Outer Join)

[#1]

Hello,

I have a table , in my example attachement Orders, that I would like to add a field to (State Name). State Name resides in the State table which is not directly associated with Orders. Orders associates with All Facts which associates with State. How can I use outer join in my script to bring in State to my Orders Table? I would then rename this to OrdersState to avoid a circular referecne. The attatment should make things a bit more clear.

Thanks!

Jeff

[jagan]

Hi,

Try like this

TableName:

select OrderID,.......... from Orders;

outer join select OrderID, FactID, StateID  from Facts;

outer join select StateID, StateName from State;

Regards,

Jagan.

[#3]

Hi Jagan,

Thanks for the response. The issue I am having is avoiding synthetic tables. So once I bring over the StateKey from the facts table, I have to rename the key ......StateKey_Orders. Then I want to use StateKey_Orders to bring over Statename. But, I can not associate StateKey_Orders to StateName since StateKey_Orders does not reside in the State table. I lose if I use StateKey_Orders bc I get an error in the load...and I lose if use StateKey bc a synthetic table is created.

My script looks like this:

TableName:

Load *

Resident Orders;

Outer Join (Orders) Load

Order Key,

StateKey as  StateKey_Orders

Resident All Facts:

Outer Join (Orders) Load

//StateKey

StateKey_Orders,

State Name

Resident State;

Thanks!!!

[jagan]

Hi,

Try this

Load *

Resident Orders;

Outer Join (Orders) Load

Order Key,

StateKey as  StateKey_Orders

Resident All Facts:

Load

//StateKey

StateKey_Orders,

State Name

Resident State;

DROP TABLE [All Facts]; // Dropping this because we already have this details in orders table.

Hope this helps you.

Regards,

Jagan.

[#5]

Sadly, in actuality the All Facts table is much larger than it is in my example. Also, it contains some data that is not 1:1 with orders . So bringing it all over to the orders table can not be done.

Thanks,

jeff

[rwunderlich · Accepted solution]

Use StateKey without renaming. After the join then:

DROP FIELD StateKey FROM Orders;

-Rob

http://robwunderlich.com