Join Query from 4 tables

[udigold1]

Hi,
I'm pretty n00b in complicated SQL statements, so I apologize in advance.
I have 4 tables:
1. table of balance sheet of equities.
2. table of tradable equities (from an outside source)
3. table of non-tradable equities (managed in the database)
4. table of corporate entities for both tradable and non-tradable equities (managed in the database)

I cannot change the design of the database and I want to to get the result detailed in table5:
How do I do that?

Thank you for your patience.


table1 - Balance Table

equity_id	FaceValue	Balance
xzy	100	500
abc	100	250

table2 - Tradable Equity

equity_id	corpId
xzy	555442

table3 - NonTradable Equity

equity_id	corpId
abc	2345

table4 - corpDesc

corpDesc	corpId
abc Inc.	2345
xzy inc.	55442

table5: desired result

equity_id	balance	facevalue	corpId	corpDesc
abc	250	100	12345	abd inc.
xzy	500	100	55442	xzy inc.

[CJ_London]

Can you confirm tables 2 and 3 are mutually exclusive - i.e. an equity can be in one table but not both

[udigold1]

Can you confirm tables 2 and 3 are mutually exclusive - i.e. an equity can be in one table but not both

On perfect world - they should be mutually exclusive
Unfortunately, there could be instances of overlapping, because table2 is from an outside source I cannot control it 100%.
I guess I need some sort of hierarchy - if not in table2 then go lookup in table2.

thanks again

[CJ_London]

you really need to think this through as it affects how the query would be constructed - what do you want happen when they are not mutually exclusive? - do they both have the same corpID field? do they both have exactly the same equity_id?