I am trying to create a view from one DW Database tables to another DW database (which is empty).
I have total 5 tables - 4 tables in one schema and another table is in another schema from same Source database.
Morg.loanNum and DevProg.loanID has 1 to many relationship.
I am getting following error:
ERROR: Msg 4104, Level 16, State 1, Line 31
--The multi-part identifier "BD.Morg.loan" could not be bound.
Because BD.Morg.loanNum and AD.DevProg.loanID has 1 to many relationship.
How can use join as i tried with LEFT OUTER JOIN, CROSS JOIN also.
My Sql is: ( i am not creating view right now as trying work on select first)
Is it I can use DatabaseName.SchemaName.TableName.ColumnName to select Fields and Data from Source Database tables to another Target Database as it's on the same server?
I am trying to create a View into Target Database using from the Source Database Tables.
I think i was able to make the query.
I was using join in wrong order as i should use Dev as driver table instead of addrs in join conditions. but only issue with this as it's pulling NULL to NULL in both the table column and i wanted to avoid it. How can I avoid it?
I have built following query:
M.loanNum, M.stat, M.OPbal, M.CPbal,
FROM AD.Dev D
INNER JOIN AD.DevProg DP ON DP.DevKey = D.DevKey
INNER JOIN AD.Addrs A ON A.AddrKey = D.PAddrKey
INNER JOIN AD.Cnty C ON C.CntyKey = A.CntyKey
INNER JOIN BD.Morg M ON M.LoanNum = DP.LoanID
but getting like: (See Park Record - which has LoanID and LoanNum both are Empty (NULL) and i don't that record which has NULL for LoanID and LoanNum.