SQL - joining 3 tables

Question

I have 3 simplified tables:

1. Balances: PartyID, PartyName, SecurityName, BalanceEUR

2. Parties: PartyID, PartyName, PartyGeneralID, PartyAddress

3. Securities: SecurityID, SecurityName, IssuerID, MaturityDate

Select
  Balances.PartyName,
  Balances.SecurityName,
  Balances.BalanceEUR,
  Parties.PartyName,
  Parties.PartyGeneralID,
  Parties.PartyAddress,
  Securities.IssuerID,
  Securities.MaturityDate,
  Parties.PartyName, --(Issuers not clients)
  Parties.PartyAddress --(Issuers not clients)
  Parties.PartyGeneralID --(Issuers not clients)

---> here I want to have Issuers details (address and generalID) from table Parties - Securities.IssuerID=Parties.PartyID

(so in final table there will be details about clients from Parties and also about issuer details from table Parties based on IssuerID I do not know how to include this recognition betwwen clients id and issuer id - so address is not clients but issuers)

from Balances
    LEFT JOIN Parties on Parties.PartyID=Balances.PartyID
    LEFT JOIN Securities on Parties.PartyID=Securities.IssuerID

Answer 1

SELECT
  BAL.partyName
, BAL.securityName
, BAL.balanceEUR
, PARTY.partyName
, PARTY.partyGeneralID
, PARTY.partyAddress
, SEC.issuerID
, SEC.maturityDate
, ISSUER.partyName AS issuerName
, ISSUER.partyAddress AS issuerAddress
, ISSUER.partyGeneralID AS issuerGeneralId
  FROM balances BAL
  LEFT JOIN parties PARTY on PARTY.partyID = BAL.partyID
  LEFT JOIN securities SEC on BAL.securityName = SEC.securityName
  LEFT JOIN parties ISSUER on SEC.issuerID = ISSUER.partyID

I am assuming this is what you are looking for? Odd that you are using a name instead of an ID to JOIN to but, if that's what you got, use it.

Answer 2

To select the parties for the Balances.PartyID you will need to join on Balances.PartyID = Parties.PartyID. To select the party for the issuer, you will need to join on Securities.IssuerID = Parties.PartyID. It's best to declare table aliases in your select statement so you know which table is used for what:

Select
  Balances.PartyName
, Balances.SecurityName
, Balances.BalanceEUR
, Parties.PartyName
, Parties.PartyGeneralID
, Parties.PartyAddress
, Securities.IssuerID
, Securities.MaturityDate
, issuer.PartyName as issuerName
, issuer.PartyAddress as issuerAddress
, issuer.PartyGeneralID as issuerGeneralId
from Balances
    LEFT JOIN Parties party on Parties.PartyID=Balances.PartyID
    LEFT JOIN Securities s on Balances.SecurityName = s.securityName -- ? Really?!
    LEFT JOIN Parties issuer on s.IssuerID = issuer.PartyID

I've assumed that you get from the Balances to the Securities by using the SecurityName. This strikes me as a horrible way, so if you know better, please edit your post and show us how exactly Balanaces and Securities relate to one another.